topic Re: How do I apply a filter that relates to a more granular lever than the one I am reporting on? in DAX Commands and Tips

How do I apply a filter that relates to a more granular lever than the one I am reporting on?

Anonymous — Sun, 22 Mar 2020 18:29:56 GMT

Reported Splits on Bar Chart with drill down ability to get form DMA to City
DMA (Denver Selected)	Distribution of DMA (Denver)	City (in Denver)	Distribution of City (in Denver)
Denver	235	Denver	70
Denver	235	Littleton	30

However, I want to put a limit on showing each DMA or City based on if they pass the distribution rule when it is also split by market so pub and cafe for example.

Littleton For example passes Distribution > 30 at a more granular level so is showing on my bar chart.
However, when split by market Littleton bars < 30 and Littleton Cafes < 30 so I do not want Littleton to show on my bar chart but i want to be able to do this without putting market in my ledgend or showing on the chart at all, just a filter or some kind of calculated column maybe?

Thanks in advance! 🙂

Re: How do I apply a filter that relates to a more granular lever than the one I am reporting on?

Greg_Deckler — Sun, 22 Mar 2020 17:53:05 GMT

I'm not at all clear on this. You could use the Filter pane to get rid of cities.

If you absolutely, positively have to do it in DAX, you can start your DAX code with something like:

Measure =

VAR __Table = FILTER('Table',[City] <> "Some city" && [City] <> "Some other city")

Then perform your calculations against __Table

Or, at the end of your measure you could do something like:

RETURN

IF(MAX('Table'[City]) = "Some city" || MAX('Table'[City]) = "Some other city",BLANK(), <whatever calculation>

Re: How do I apply a filter that relates to a more granular lever than the one I am reporting on?

Anonymous — Mon, 23 Mar 2020 23:30:20 GMT

Anonymous, would you mind giving us a more understandable description with some simple data and the result you want?

This will help us solve your problem more easily and quicker.

Thanks.

Best
D

Re: How do I apply a filter that relates to a more granular lever than the one I am reporting on?

Anonymous — Wed, 29 Apr 2020 13:19:56 GMT

Thanks all,

I figured this out using a SUMX

Distribution_Segment_Limit =

SUMX(VALUES('Table'[Segment]),

[Distribution])

Where

Distribution =

VAR Dist = DISTINCTCOUNT('Table'[ID])

RETURN IF (Dist >= 30,Dist,Blank())

From there I was able to plot my regions on a barchart without putting segment into the breakdown but the distribution was not included if it did not pass the distribution rule criteria for region and segment.