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
Anonymous
Not applicable

How to ignore a slicer for one measure, but apply it on another?

Hello, 

 

I'm new to power bi and DAX so I'll try to be as clear as I possibly can.

Here's the graph I'm dealing with:

graph.png

The X axis shows the hours of the day. Each bar shows data of a specific location, yet it's possible that two different bars would show the same location because of a time difference (hours on the X axis).

What I'm trying to do is to SUM a cloumn, grouped by location.

 

Example:

The number 704 (registered users) shown on the third bar (tooltip) also apears on the second because it's data from the same location. In this case I only want to sum it once. The first bar shows 540 registered users from a different location, so in total, the new measure I'm trying to display across all bars would show the number 1244 (704+540).

 

Any kind of help would be appreciated.

Gilad

6 REPLIES 6
Anonymous
Not applicable

An alternative solution would be to just use ALL() and then filter that data using the current values in the slicers? How do I do that?

Without sample data to recreate this scenario locally, not sure I can help you further.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Greg_Deckler
Super User
Super User

Take a look at CALCULATE and ALLEXCEPT as a filter clause. Basically, you can calculate your measure and specify which filters to include. Essentially, you are overriding the context in which the measure gets calculated.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

That's exactly what I've been trying numerous times, but the numbers don't add up:

CALCULATE(SUM(registered_users), ALLEXCEPT(hours)))

What is wrong here?

I believe what you want is ALLEXCEPT([Location]). This would filter keep the location filter but ignore the hour filter, which I believe is what you want.

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

It doesn't solve my problem unfortunately.

The only way I'm able to get the number I want (1244) is when I change the time field under 'Axis' to Date Hierarchy.

 

graph2.png

 

Only then the graph displays one bar, instead of the three bars i wanted, which is sliced by hours.

But when I do change that property, additional visual level filters apear with more date levels such as year quarter month and so on.

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.