Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Bernd
Helper I
Helper I

DAX complementary percentage with filters

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:

 

DateTimeVal_1Val_2
date11:0035
date12:0047
date21:0069

 

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:

  1. Sum of Val_1 := CALCULATE(SUM('fact'[Val_1]), ALLSELECTED('calendar'[Date]))
  2. Sum of Val_2 := CALCULATE(SUM('fact'[Val_2]), ALLSELECTED('calendar'[Date]))

In order to calculate the complementary percentage I use the following measure

  • CompPercentage (%) := CALCULATE ( 1 - DIVIDE([Sum of Val_1), [Sum of Val_2]), ALLSELECTED('calendar'[Date])

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?

 

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

 

 

 

View solution in original post

4 REPLIES 4
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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:  image.png

 

with date filter: image.png

 

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.