Variable Measures?

[chrismiller]

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.

[Anonymous]

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...

[chrismiller]

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

[Anonymous]

Can you add a screen shot of your model relationships?

Not sure how DimDate/MonthSelect and your fact table relate.

[chrismiller]

sure no problem, hope this helps.

[Dog]

Hi, 

hopefully I'm not confusing the issue further but as you are referring to and passing the dates for selected month do you need the relationship between selectedmonth and dimdate. 

if its used for other purposes would it work by passing the ALL('DimDate') into the filter of your measure to avoid the filtering when selected. 

NETWORK DAYS 2017_variable = CALCULATE(COUNTROWS('DimDate'),ALL('DimDate'), FILTER('DimDate', 'DimDate'[Day of Week Number] < 6),DATESBETWEEN(DimDate[Date],DATE(2017,[MonthNumSelect],1), DATE(2017,[MonthNumSelect],[EndofMonthday])))

[chrismiller]

Thanks for the reply, if I am understanding your first question; I I have the relationship between selectmonth and dimdate because  the rest of the report uses dimdate and the graphs and slicers I would be using to filter this are all using dimdate; that being said I have tested and the relationship workd and the results are identical whether is filtered via dimdate or select month

I inserted the formula you gave and it didnt make the problem go away.  Is there something else I should change first?

[Dog]

hmmm that would suggest that the filter may not be responsible 

if you add the networkdays measure into a card on the report page are the day numbers changing as you expect them too. 

[chrismiller]

yes exactly! and the table below is actually much wider with several different networkdays (i just included the one for simplicity) when i change the filter all the network days change but not the formula/measure that they are in.

 

[Dog]

is the select month in a filter on the report page. 

if so can you try changing the ALL('DimDate') to ALL('SelectMonth')

NETWORK DAYS 2017_variable = CALCULATE(COUNTROWS('DimDate'),ALL('SelectMonth'), FILTER('DimDate', 'DimDate'[Day of Week Number] < 6),DATESBETWEEN(DimDate[Date],DATE(2017,[MonthNumSelect],1), DATE(2017,[MonthNumSelect],[EndofMonthday])))

although I'm still not entirely sure I follow.... and no doubt am probably frustrating you further 

[chrismiller]

no frusteration at all. this report has sent me sideways.

these might explain it

NETWORK DAYS 2017_variable2 and 3 are the two formulas you provided.

When nothing is selected they all show the default value for april (first in the list and with 20 days) with the exception of the one which is sorted by month number so 21 days in january.

They are all slightly different formulas but essientially functionally identical.

one slicer is for dimdate and the other is for selectmonth

[Dog]

ok... just so I can have it clear in my head. 

of the 3 screenshots you sent which if the outputs are wrong and what should they be? 

also to save me going backwards and forwards over the screens on here would you mind including hte measure text of those wrong ones please? 

Thanks

D

[chrismiller]

the first two are wrong or atleast show error. Because when they are filtered to march they should show 23 network days which they do. as well if its not filtered it should show the default number of network days, which they do (21 or 20 depending on if its alphabetical order april=20 or numerical january=21).  

The error is with the 'WFP YTD Ratio 2017' it should equal the number to the left of it (0.881)

NETWORK DAYS 2017_variable2 = CALCULATE(COUNTROWS('DimDate'),ALL('DimDate'), FILTER('DimDate', 'DimDate'[Day of Week Number] < 6),DATESBETWEEN(DimDate[Date],DATE(2017,[MonthNumSelect],1), DATE(2017,[MonthNumSelect],[EndofMonthday])))

=23 (when filtered for march, otherwise 20 by default)

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,(([NETWORK DAYS 2017_variable]*8)/[TOTAL_HOURS]), 0)) 

= .0766 

heres the issue; if I change it and hardcode the network days in as 23 (the way it shows on the chart) it will return the correct cumber (0.881)

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,(((23)*8)/[TOTAL_HOURS]), 0)) 

=0.881

(conversly if I hardcode it to 20 it will display 0.0766)

so for whatever reason when networkdays is nested in another measure it doesnt display the same number, just the default.  It is not lost on me that it may be a function of my approach to monthnumselect:

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)))

where in certain instances it doesnt acknowledge a filter?  I say this as at one point I attempted to use if(hasonevalue  as it would often return it as if it doesnt have one value (even though it was filtered)

Hope this answers any further questions I know it is confusing.

[Dog]

Hi, 

out of interest what makes up the measure [EndofMonthday]

Thanks

[chrismiller]

EndofMonthday = DAY((EOMONTH(date(2017,[MonthNumSelect],1),0)))

[Anonymous]

So... I see some bidirectional filters turned on in your model.  I trust those things just as far as I can virtually throw them.

Before we debug further, you mind just turning those relationships to "Corss filter direction" = Single, instead of "Both" ?

[chrismiller]

Ok, I changed all the filters (it would let me to single) although I think the only relevant tables in here is cap targets2017 and a measures table.

[Anonymous]

Sounds like changing away from bi-directional filters didn't magically fix your problems.   I was so optimistic... 😞