Assignment # 10 : DAX Measures and Calculated function using SWITCH

#switch #powerqueryeditor #powerbi #powerbiservice #dax #functions

Nishit Joshi Feb 01 2021 · 1 min read
Share this


Power BI is the newest Microsoft BI tool for data mash up, modelling, and visualization. It supports mobile devices, self-services, and along with its great functionalities, it doesn’t require heavy development or production system.


It seems that the more aged person, the more no. of breaks they are going to get.


Create a calculated column to calculate average number of breaks for each number of films the number of breaks needed.

Create a matrix showing Oscar Wins with Average of Number Breaks Column

Source Report: Comfort Breaks, List of Films

1.)    Upload files Comfort Breaks and List of Films through Get data to Power BI.

2.) Go to Film Table > Create new column > Use the DAX Function with SWITCH(TRUE(),…………… to find out the number of Breaks.

Number Breaks_1 = SWITCH(TRUE(),Films[RunTimeMinutes]<90,0,Films[RunTimeMinutes]<150,1,Films[RunTimeMinutes]<210,2,Films[RunTimeMinutes]<300,3,Films[RunTimeMinutes]>999,4,5)

Number Breaks_2 = CALCULATE(VALUES(Sheet1[Breaks]),Sheet1[LowerLimit]<=EARLIER(Films[RunTimeMinutes]),Sheet1[UpperLimit]>EARLIER(Films[RunTimeMinutes]))

3.)    Make sure that the Data Model is connected to Films, if not then you have to do it manually by visiting Data Model tab on left side of Power BI software. Drag Breaks from Sheet 1 to Number Breaks_1 under Films Table.


Ø  According to the evaluation we can conclude couple of things :

Ø  The more Budgeted Dollars and Box Office Dollars Oscar winners are really less compare to low budget Dollars which means that the content, cinematography, production, photography, Acting, and direction is more important.

Ø  Also the Box Office Collection don’t seem to be high because of the content which might not attract all the generation, kids and other public to watch the movies.

Ø  Oscarwins by Genre charts shows the top winning Oscar movies are related to Drama.

Ø  Top 5 Genre in Oscar Movies are

Drama = 107

Musical = 53

Science Fiction = 51

Adventure = 41

Crime = 40

Romance = 40

Read next