Forum Discussion

JL83's avatar
JL83
Frequent Visitor
2 years ago

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!
Need Help

3 Replies

  • parry2k's avatar
    parry2k
    Super User
    2 years ago

    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! 

    • JL83's avatar
      JL83
      Frequent Visitor
      2 years ago

      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. 

       

  • parry2k's avatar
    parry2k
    Super User
    2 years ago

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