Forum Discussion
Small Multiples Breaking DAX Measure
Hello, I have the measure i have here below works as intended when i apply visual level filters to my visual. However, when i add my column dimCalendar[Day_of_week] to small multiples or as a column or row in a matrix, my results are incorrect.
For example, In October of 2023 i expect to return 5 Mondays, when the visual level filter is applied this works fine. When placed in a small multiple it returns 25, which throws my calculation off.
Any guidance or explanation as to why this happens would be appreciated.
Thanks,
Avg Subs Sat as BD =
VAR StartD=Min(dimCalendar[DAY_OF_MONTH])
VAR EndD=Max(dimCalendar[DAY_OF_MONTH])
VAR TotalBDInPeriod=CALCULATE(
COUNTROWS(dimCalendar),
FILTER( ALLSELECTED(dimCalendar), dimCalendar[BUS_DAY_FLAG]=1||dimCalendar[DAY_OF_WEEK]="Sat"), dimCalendar[DAY_OF_MONTH]>=StartD&&dimCalendar[DAY_OF_MONTH]<=EndD
)
Return TotalBDInPeriod
4 Replies
Hi Mr_RLE
Would something like this help?
Avg Subs Sat as BD = VAR StartD = MIN( dimCalendar[DATE] ) VAR EndD = MAX( dimCalendar[DATE] ) VAR TotalBDInPeriod = COUNTROWS( FILTER( ALLSELECTED( dimCalendar ), ( dimCalendar[BUS_DAY_FLAG] = 1 || dimCalendar[DAY_OF_WEEK] = "Sat" ) && dimCalendar[DATE] >= StartD && dimCalendar[DATE] <= EndD ) ) RETURN TotalBDInPeriodThanks gmsamborn , I appreciate the assistance. Unfortunately this doesn't seem to correct my issue.
This seems to return the same as the measure i have currently, outside of the calculation for Sunday which is better but still not the correct number.
Day of Week My Measure Your Measure Mon 25 25 Tue 25 25 Wed 19 19 Thu 19 19 Fri 19 19 Sat 19 19 Sun 29 24 Thanks for helping. I am not sure i can share a pbix as my data contains PII for some work clients.
I am using an area chart with the small multiples, but it also breaks in a matrix if i add my [day_of_week] column to the matrix as a row or column. if it's applied as a visual filter it doesn't break.
Essentially what i need to do is use the total business days in a period as the denominator for an average of customer tickets. I want to be able to slice this by day of week to provide that detail to my end users.
