Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Specialist707
Frequent Visitor

Count multiple column values meeting criteria and Custom Filter

Hi,

 

I'm trying to create a stacked bar chart that has a legend to segment between "Covered" and "Not Covered". However, I need the values of each segment to be dynamic based on a filter selection, and have the ability to select each segment on the bar chart to then show the detail in a separate table (e.g If i select "Covered" for the computer and/or product segment of the bar chart, the other table will filter for only those that are "covered", and vice versa)

 

Note the 1 represents if it is covered by that provider, and the 0 is not covered. The original concept I designed was fairly static, so thats why the overall coverage column is there. As mentioned previously, I'm looking for that column to be dynamic based on the selections (e.g For product B, if Apple and Microsoft are selected, then "not covered", but if Apple and Google are selected, then "covered"). If atleast one of the companies is selected in the slicer and one of them has a value of 1, then it would be considered covered.

 

UnitProductAppleGoogleMicrosoftTeslaOverall Coverage
ComputersA1011Covered
ComputersB0100Covered
ComputersC1111Covered
ComputersD1101Covered
PhonesE1000Covered
PhonesF1111Covered
PhonesG0101Covered
PhonesH0101Covered
PhonesI0000Not Covered

 

Is there a solution be available that would allow me to select the companies (Apple, Google, Tesla, Microsoft) via slicer (one aggregated one) and based on the selections, it would return covered or not covered in a segmented bar chart fashion, but allow for the filtering to be done at the covered/not covered level in the bar chart? I'm not sure on the exact DAX logic, but I would imagine it would be something like "If Apple and Tesla are selected, and the sum of both columns is >1, then 1, else 0". I'm just not sure how to account for the filtering aspect of the bar chart that I'm looking for. Perhaps a disconnected Covered/Not Covered table that acts as a legend (although If its disconnected, im not sure the filtering by segment in the bar chart would work)?

 

I'd like the bar chart to look something like this, which is currently using measures as the x-axis, but it doesnt allow for filtering since the two segments are measures: 

Specialist707_0-1678369770813.png

 

 

Really appreciate any help/guidence! 

1 REPLY 1
PiEye
Resolver II
Resolver II

Hi @Specialist707 

 

You shouldn't need to have two separate expressions. 

 

Overall coverage field can be dropped into the legend and will work with one measure, which I have added in the below example as a generic count:

PiEye_0-1678446548690.png

This will be sensitive to selections.

 

Is this what you were looking for?

 

Pi

 

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors