Forum Discussion
Variable Measures?
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?
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.
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.
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" ?
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
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.
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
Sounds like changing away from bi-directional filters didn't magically fix your problems. I was so optimistic... :(
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.
Hi,
out of interest what makes up the measure [EndofMonthday]
Thanks
EndofMonthday = DAY((EOMONTH(date(2017,[MonthNumSelect],1),0)))