Forum Discussion
Variable Measures?
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
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.