Measure to filter Value counts for Visuals

[kylifeofpy]

Hi PowerBI Community,

Running into a problem that I’m unsure if it can be solved with a measure or is it required to have an added column, which won’t work for me since I’m using Direct Query mode, so I’m limited with that regards. It’s rather a simple issue: I’m using a table column for a pie and stacked bar chart, but a row of data needs to be filtered based off another column of the same table, so the results of that filtered value count is represented in the visuals, see below screenshot and OneDrive link to pbix file with dataset. Again, the pbix file is obviously using import mode for troubleshooting this problem, but the real data is direct query. Although, if there isn’t any other way, I’m wondering if dynamic segmentation and the possibility of using DAX to generate a virtual table from the direct query tables can resolve this…

For the desired result:

• Page-level filtering can be applied to the ‘Dept’ column to display ‘IT’ only - solves the issue with ID 1
• Visual-level filter to show only ‘Title’ with ‘Consultant III’ and “Consultant IV’ OR ‘Title’ column where the individual is not ‘Consultant III’ or ‘Consultant IV’ AND ‘ID Badge’ column is not NULL – already solved with ‘Filter Measure’ at the dataset’s table visual’s visual pane by setting the filter to: 1
• ID 4 with title Consultant II should NOT be displayed on my visuals (use desired result pie and stacked bar chart as reference) - main concern

The pie chart is using count of ‘Employment Status’ column as it’s Values, and same with the stacked bar chart’s ‘X-axis’. It’s because of this that it doesn’t consider any filters I’ve applied on the table visual (aside from the dept filter, but again the concern is the row with ID 4). The question is: Is there a measure that returns the ‘Employment Status’ column but filtered to only individuals with ‘Title’ = ‘Consultant III’ or ‘Consultant IV’ that can be used for these visuals?

A measure that’s capable of doing that is only what I believe to be the solution, but if there’s a better one that works with Direct Query mode, I’m all ears. Thank you

Measure to filter value counts for visuals.pbix

[kylifeofpy]

Hi @isjoycewang This is great! I love how it's more consolidated than applying a separate countrows DAX query, which was also provided to me as a potential solution from the Stack Overflow community. Apparently the answer was right in front of me the whole time and I'm astonished that it can be used on visuals, so thank you very much for the streamlined answer and your assistance.

[isjoycewang]

Glad to help!

You already have a complete filter condition wrote in the [Filter Measure] so I think we can take advantage of this to have a easier formula 🙂 

[isjoycewang · Accepted solution]

Hi @kylifeofpy,

Please try below measure and apply it to your pie chart or column chart.

Measure = 
COUNTX( 
    FILTER(IdentificationBadge, [Filter Measure]=1), IdentificationBadge[ID])

Best Regards,

Joyce