Click on Parameter, line and clustered chart and filter are not responding

[Anonymous]

Good evening All,

Here is a ZTA.pbix file.

https://drive.google.com/drive/folders/19XWky9rj5hgF5MV-Gqfr29BN8-74Y4vn?usp=sharing

I created the parameter field "Pillar List" and added it to the X-axis of the Line and clustered chart.  When I clicked on the "Identity" in the Pillar List, the line and cluster chart were not responding (I already checked the Format->Edit interactions, and it showed ok). The filter showed the field "Identity" with Blank and Identity values.  Also, the filter kept the previous selection, "Network" (I had to remove it to make the chart show Identity data).  I don't know why the filter has the "blank" value for every pillar item. Why do I have a Blank value on the filter for each Pillar item?  How can I remove them from the filter?

I like to have the output that when I click on Identity, the chart will respond, the filter will not ask me to uncheck the blank value, and the filter does not keep the previous selection. 

Thank you very much for your help. 

[Anonymous]

Hi @hnguy71 

Thank you so much for your reply.  I love your models.  I don't know how to display only (create the measure) the maturity level value at each pillar per year when putting them on the graph. For example, Pillar Identity's maturity level from 2025 to 2028 is 1,1,2,3.  How to create the measure to display the exact value: 1 for the year 2025, 1 for 2028, 2 for 2029, and 3 for 2030.  I use the measure Min to take the minimum value for the Identity pillar, which is incorrect.

[hnguy71]

Hi @Anonymous 

Not going to lie... your model needs some rework... this would benefit you down the road by simplifying your model.

You should combine / union all your "regular" tables into one singular fact table.  If you need to distinguish each "site" then add another column for it. Here's an sample:

Fact = 

VAR _ABC = ADDCOLUMNS(ABC, "SiteId", 1)
VAR _DEF = ADDCOLUMNS(DEF, "SiteId", 2)
VAR _HJK = ADDCOLUMNS(HJK, "SiteId", 3)
VAR _LMN = ADDCOLUMNS(LMN, "SiteId", 4)
VAR _PHK = ADDCOLUMNS(PHK, "SiteId", 5)

RETURN

UNION(_ABC, _DEF, _HJK, _LMN, _PHK)

In this way, you get one table that you can query and execute against insteading of creating all of these crazy "parameters". All your pillars, functions, capabilities, etc are now all in one place and you can apply just a simple slicer against your table:

And, if you wanted to query all the information relating to a specific "site", you can apply a filter against this singular table:

Of course, you would still need your "Site" dimension and a date table but the rest should be simplified. Fix that and your referential intergrity issue with the blanks should automatically go away.

[Anonymous]

Thanks, Hnguy71, for your reply.  I will try to do it.  

Also, as I mentioned above, I would like to display the maturity level on the chart. When I click on identity, it shows only the identity maturity level on the chart without clicking on the filter.  Do you have a method to do that?

[hnguy71]

Hi @Anonymous 

Same concept. append all your "maturity" tables together and create a measure to return the maturity level based on the selected pillar, function, or capabilities selected.

I just want to continue to stress that you should centralized dimension tables to return all the attributes that can talk to all tables. However, for simplicity sake you should have a basic model similar to this:

And with some small effort, you can pair both maturity level and dollar value together:

[Anonymous]

Can you please share your .pbix file?  I like to see what is different from yours and mine.  Thank you so much for your help. 

[hnguy71 · Accepted solution]

HI @Anonymous 

Unfortunately I didn't save any working from your model. As long as you can replicate your model to look like the screenshots you should be in the clear.

Provided to you was an example of how you can achieve it, but your real model should have all the data transformations done in the back-end within powerquery

[Anonymous]

Thank you so much for your valuable time and effort in working on my problem. I appreciate your help. I fixed the error in the Excel spreadsheet. Before I imported spreadsheets to PowerBI, they created space in the spreadsheet.