Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have two tables: 'fact' and 'calendar'. 'Fact' contains [Date] and [Time] with multiple rows per day and has a relationship with 'calendar' in the standard way. The fact table looks like this:
Date | Time | Val_1 | Val_2 |
date1 | 1:00 | 3 | 5 |
date1 | 2:00 | 4 | 7 |
date2 | 1:00 | 6 | 9 |
I need to calculate the complementary percentage of the sum of Val_1 over Val_2 by day. So I have two measures to calculate the sum of the Val-columns by date:
In order to calculate the complementary percentage I use the following measure
which works find as long as I do not filter on dates. As soon as I filter on [dates] the measure returns 100% for all dates for which there is data in the 'fact' table and the correct value for all filtered dates. Therefore I introduced the ALLSELECTED filter, but it seems to be ignored. I also tried VALUES on [Date] in the 'fact' table, but also without success.
How can be get the date filter applied to the CompPercentage measure correctly?
Solved! Go to Solution.
Hi @v-shex-msft,
the only workaround I found was adding an if clause to show blank values for all dates that were not selected and therefore would return blank for [Sum of Val_2]:
CompPercentage (%) :=
IF (
ISBLANK ( [Sum of Val_2] ),
BLANK (),
1 - DIVIDE ( [Sum of Val_1], [Sum of Val_2] )
)
I have no idea though why the ALLSELECTED filter does not provide the same result.
Hi @Bernd,
It seems like you have posted the wrong formula, I modify it and use below formula to test.
CompPercentage (%) := CALCULATE ( 1 - DIVIDE([Sum of Val_1], [Sum of Val_2]), ALLSELECTED('calendar'[Date])
Based on test, I can use filter to change the result. Can you please share some detail content?
In addition, I'd like to suggest you modify your relationship settings if the cross filter direction is set to "single".
Regards,
Xiaoxin Sheng
Thx. @v-shex-msft,
yes, I missed a bracket at the end. The relationship is set to "single". I even rebuild the whole thing from scratch, but still get the same result:
w/o date filter:
with date filter:
So not sure how you could manage to make this work such that in the second case no value for May 2 is shown rather then 100%?
Hi @v-shex-msft,
the only workaround I found was adding an if clause to show blank values for all dates that were not selected and therefore would return blank for [Sum of Val_2]:
CompPercentage (%) :=
IF (
ISBLANK ( [Sum of Val_2] ),
BLANK (),
1 - DIVIDE ( [Sum of Val_1], [Sum of Val_2] )
)
I have no idea though why the ALLSELECTED filter does not provide the same result.
Hi @Bernd,
Please use the same date column in table visual and slicer. if table use the "fact[date]" and the slicer use "calendar[date]", the issue will appeared and the filter seems not work.
Regards,
Xiaoxin Sheng
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |