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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
JL83
Frequent Visitor

5 Measure bar chart visual filter to a data matrix

 

I have a fairly complex dashboard where I have 5 individual measures creating a bar chart. The bar chart is showing counts of values from a matrix below the bar chart. I will explain each of the visualizations and measures then what I am looking to have done.

 

Bar chart measures:

FordCount = IF(SUMX(DISTINCT(TBL1[Col1]),[Measure1])=BLANK(), 0, SUMX(DISTINCT(TBL1[Col1]),[Measure1]))

These measures incorportate additional measures that priovde a 1 or 0 based on true/false of the filter in the measure.
 
The matrix:
The matrix is setup with static data columns for rows (DealerName) and columns (State). The value is a measure which checks the measures from the bar chart applies a car brand based if the value = Blank(). I do not need the column or rows to change ever, these can be static as I have a slicer specific for filtering each of these. The value measure is a nested IF statement for the 5 measures in the bar chart:
Example:
IF([FordCount]<>Blank(), "Ford",
IF([GMCount]<>Blank(), "GM",
IF([NissanCount]<>Blank(), "Nissan",......
 
 The goal would be to click one of the bars from the bar graph "Count of Fords" and have the matrix show only where the word "Ford" appears or you select "Count of Ford" bar and "Count of GM" bar and the matrix would only show where "Ford" and "GM" arappears.

I cannot use static columns or tables, this must be fully dynamic.  I had a solution which used a DAX table but you could not change the slicers as the matrix and bar chart would not calculate correctly when a bar was selected. I know this probably looks odd but this is what has been asked. I also cannot pass the dashboard due to confidentiality of the information within. 
 
I have tried searching for a solution with no luck. Many thanks in advance!
3 REPLIES 3
parry2k
Super User
Super User

@JL83 sorry, without looking at the data I cannot assist/help. I hope someone else can. 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

parry2k
Super User
Super User

@JL83 Since you already mentioned that you cannot share the report but I think it will be easier if you create dummy data in a pbix file with the existing measures you have and provide the expected output, it will be easier to provide the solution. From your post, it is very hard to understand the problem and provide a solution. Again, maybe it is me, and probably someone else can provide a solution based on your post. Good luck! 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

JL83
Frequent Visitor

Thanks parry2K.

 

The problem is I cannot get the matrix table values to filter when I select one of the 5 measures in the bar chart.


I cannot use a DAX created table as it's static (I tried making a table of distinct names). When I tried this the end result was close but using a static table didn't allow the matrix to update when you changed a slicer. i.e. while the matrix value changed from Ford to GM, GM was still showing even though the bar for "Count of Fords" was selected.

Let me see what I can do for a dummy data set. The source is 11 columns but I have well over 20 measures for different statisical evaulations, KPIs, etc. I might have another dashboard similar to this that uses a static table for the bar chart and there is no data privacy concerns. Let me see if I can get it cleaned up to represent what I am looking for. I won't have time to get it done until tomorrow, just FYI.. don't sit and wait for it. 

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.