Get aggregate with several tables while excluding a field

[naledi_h]

I am working with survey data. I have several charts that show the count of respondents (marked in green) across one of the purple fields marked in the data model. They are then split by one of the two red fields.  

Looking at the example below, I have the count of respondents for each city, split by Likert scale to get the distribution across cities. I now need to exclude any city that has fewer than 30 respondents overall.  So this would be just the number of respondents by city, excluding Likert scale. If I just include a filter for number of participants it acts on the lowest level, which is the Likert scale, so I need to create a calculation. 

I started to do this with an ALLSELECTED, but got stuck because I am working with multiple tables. Ideally I will have just one measure that returns the number of participants if this is 30 or larger regardless of if it's city, demographic group or response 1 that is  being used as the aggregate.

[naledi_h]

I was able to get around the original issue. From what I understand ALLEXCEPT() would have the same problem with columns coming from multiple tables. I don't quite understand how the FILTER function would solve this issue as I am not trying to apply a specific filter, just for a column to be ignored. 

I have the same issue again. I want to pass all filters into my tooltip but am trying for the chart total to then ignore the filters on two fields that are from different tables. The year gets passed in as a slicer, the Likert scale needs to be removed so that the hover action doesn't just show the result of the response but the whole question when I hover over part of the stacked bar. 

What I am trying to achieve is that when I select a question on chart 1, which filters chart 2, this question filter is also passed on to the tooltip when I hover over chart 2. I was hoping that this would be possible by applying all filters and then excluding the ones I don't want to pass through. 

[v-stephen-msft · Accepted solution]

Hi @naledi_h ,

You can use ALLEXCEPT() to exclude some fields.

Measure =
CALCULATE ( [EXPRESSION], ALLEXCEPT ( 'TABLE', 'TABLE'[FIELD] ) )

Or use like 

Measure =
CALCULATE (
    [EXPRESSION],
    FILTER ( ALL ( 'TABLE' ), [FIELD] = MAX ( 'TABLE'[FIELD] ) )
)

Measure =
CALCULATE (
    [EXPRESSION],
    FILTER ( ALLSELECTED ( 'TABLE' ), [FIELD] = MAX ( 'TABLE'[FIELD] ) )
)
​

 ALL() vs ALLSELECTED()

ALL function (DAX) - DAX | Microsoft Learn

ALLSELECTED function (DAX) - DAX | Microsoft Learn

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.