Forum Discussion
Variable Measures?
Hello first time posting,
So I have a powerBi report and wanted to creat a card that showed performance that would change depending on the month selected.
The Performance measure contains a (confusing) network of other measures, however only two are needed to contain the variable values (that is the measure changes depending on the filter selected) One is working fine the other isnt:
My method was by creating a table (SelectMonth)
From there I made some measures to do a lookup on the table:
Basically to say select whichever is first on the table, and if the slicer is active it will be that one, if it is inactive it will be april (alphabetical order)
MonthNumSelect = SWITCH( FIRSTNONBLANK(SelectMonth[Month], SelectMonth[Month]), "January", Month(Date(2017,1,1)), "February", MONTH(Date(2017,2,1)),"March", MONTH(Date(2017,3,1)), "April", MONTH(Date(2017,4,1)),"May", MONTH(Date(2017,5,1)), "June", MONTH(Date(2017,6,1)),"July", MONTH(Date(2017,7,1)), "August", MONTH(Date(2017,8,1)),"September", MONTH(Date(2017,9,1)), "October", MONTH(Date(2017,10,1)),"November", MONTH(Date(2017,11,1)), "December", MONTH(Date(2017,12,1)))
EndofMonthday = DAY((EOMONTH(date(2017,[MonthNumSelect],1),0)))
This is an example of the first measure that is working:
Capital Actuals YTD 2017 = CALCULATE(SUM(MERGED[HOUR_QTY]), MERGED[Type]="Capital", MERGED[SOURCE]="PeopleSoft", DATESBETWEEN(DimDate[Date],DATE(2017,[MonthNumSelect],1), DATE(2017,[MonthNumSelect],[EndofMonthday])))
However the one that causeing trouble is 'network days' which simply states the number of work days in a month:
NETWORK DAYS 2017_variable = CALCULATE(COUNTROWS('DimDate'),FILTER('DimDate', 'DimDate'[Day of Week Number] < 6),DATESBETWEEN(DimDate[Date],DATE(2017,[MonthNumSelect],1), DATE(2017,[MonthNumSelect],[EndofMonthday])))
Here is where it gets confusing...
the number on the right (0.0881) is right, its for march. I have the slicer/filter set to march so the network days (23) is RIGHT however the WFP YTD Ratio 2017 is WRONG (it should be same as left; 0.0881)
Note this forumula contains [NetworkDays2017_variable]
WFP YTD Ratio 2017 = IF('Cap Targets 2017'[WFP Flag2017]=1, 'Cap Targets 2017'[WFP Days2017]*8/[TOTAL_HOURS], IF('Cap Targets 2017'[WFP Flag2017]=0,(([NetworkDays2017_variable]*8)/[TOTAL_HOURS]), 0))
So what is happening is the slicer is effecting [NetworkDays2017_variable]=23 (march number) as is should but when it is embedded in to the formula it is returning aprils number (20); that is if I remove [NetworkDays2017_variable] and hardcode '20', WFP YTD Ratio 2017 will equal what it currently shows and if I hardcode '23' it displays 0.0881 as it should.
Simply put the measure shows the 'SelectMonth' table being filtered to show the 'first non blanc' which is the only line but when it is embedded in another measure it shows the first line because appearently the table isnt being filtered.
Sorry for the novel, I have been up and down the forums looking for a soltion.
17 Replies
- AnonymousNot applicable
I feel you are working way too hard on this :)
Your MonthSelect table already has a MonthNumber and MonthDate (start of month).
MonthNumSelect could be just MIN(MonthSelect[MonthNumber]) ... no?
I would add EndOfMonth and NetworkDays as calculated columns on your MonthSelect table, and write really simple measures based on that...
- chrismillerHelper I
MIN(MonthSelect[MonthNumber]) is pretty much functionally identical to the one I have ... as when the filter is on it will display only the month filtered.
However still causes the same problem in my 'WFP YTD ration" being off (except the new number is slightly different as it reflects januarys network days)
I made the calculated columns but cannot put them into the measure as it doesnt refer to a min max etc
- AnonymousNot applicable
Can you add a screen shot of your model relationships?
Not sure how DimDate/MonthSelect and your fact table relate.