cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Gilladriel Frequent Visitor
Frequent Visitor

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
Super User
Super User

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

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.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Gilladriel Frequent Visitor
Frequent Visitor

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

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?

Super User
Super User

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

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.

 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Gilladriel Frequent Visitor
Frequent Visitor

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

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.

Gilladriel Frequent Visitor
Frequent Visitor

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

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?

Super User
Super User

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

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


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!