Measure to filter Value counts for Visuals

Question

Hi Stack Overflow PowerBI/DAX 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…

Screenshot: Dataset to the left and Desired Results to the right

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

OneDrive link to PBIX file with screenshot's sample dataset: Measure to filter value counts for visuals.pbix

Answer 1

Create a new measure:

Employee Count = CALCULATE(
    COUNTROWS('IdentificationBadge'),
    'IdentificationBadge'[Title] IN {"Consultant III","Consultant IV"} || 
    NOT ISBLANK('IdentificationBadge'[ID Badge])
  )

Then use this new measure for the values of your pie and x-axis of your bar chart. No need for visual filter in these cases.