Problem Statement:
Based on above data, create as follows:
Solution:
Import both the files (List of Films and ComfortBreaks one after other
Method1:
Create a new column:
v_no of breaks = CALCULATE( VALUES(Sheet1[Breaks]), Sheet1[LowerLimit]<= EARLIER(Films[RunTimeMinutes]), Sheet1[UpperLimit]>EARLIER(Films[RunTimeMinutes]) )
Method2:
Create a new column:
v2_no_of_breaks = SWITCH(TRUE,Films[RunTimeMinutes]<90,0,Films[RunTimeMinutes]<150,1,Films[RunTimeMinutes]<210,2,Films[RunTimeMinutes]< 300,3,4)
The CALCULATE function is needed because you must create a filter context for each row so that you’re only considering that particular row– without it, you would get the total for all rows same.:
NOTES:
Earlier function will return the value of the current row in a column. It works like a correlate subquery in database. The EARLIER Function, takes the first date value and compare it with other values , checks for the specified condition (<= or >=) in the Filtered row Context, if the condition satisfies that value will be includes in to the “VALUES” calculation. This iteration will repeats till the last date value. So in our case, it picks the row from column Sheet1(Breaks) for the matching condition only. greater than and less than
CHARTS /VISUALS:
Once both the calculated columns are created, which are exactly similar, we will go to the Report view and use a ‘Matrix’ visualization to get the required output. We will use the parameters as shown below in the image.
To get the average of NumberBreaks, we will use the drop-down and select the ‘Average’ option and hence the required task is completed.
Final chart will look as below:
Creation of new calculated columns is completed using DAX and we can determine the following two insights from it:
1. In the movies which have won 11 Oscars(3 movies), the author had to take on an average of 2.33 breaks while watching.
2. For the movies which have won 0 Oscars, the author had to take only 1 break on average.
