Have Measure Drop off of Matrix if value is blank

[kewaynes]

I have a matrix that has 3 measures in it. I am using the 'Show Values in row groups rather than columns' feature. When the slicers are selected, I want any of the 3 measures that don't return a value (aka blank), to disappear and fall off of the matrix. However, they currently just stay and show blanks. Is it possible to have them disappear other than using conditional formatting?

[OwenAuger]

@kewaynes thanks for the extra info.

Using field parameters does slightly complicate things 🙂

The constraint with Power BI visuals is that blank measures cannot be hidden so, whatever method we use, we will have to have values of a column standing in for measures in some way (since they can be hidden).

If you are using field parameters to allow switching/filtering of measures and you want to hide blank measures, I think you will still have to go for either

• Calculation groups (with one calculation item per measures), or
• A measure-selection measure using SWITCH (or similar), based on a parameter table.

Using the Calculation Group approach, there would be a bit of setup. The steps I would suggest are:

1. Create a calculation group corresponding to each field parameter table.
2. Name each calculation item the same as the field parameter field names (normally first field parameter column) and set the expression to return the corresponding measure using in the field parameter.
3. To get the filtering to work in the same way it is with field parameters, unfortunately calculation groups cannot have relationships with other tables. You would need to follow a method similar to this article for each visual where the calculation groups are used:
   https://www.sqlbi.com/articles/filtering-calculation-items-in-a-slicer/

If you instead used the measure-selection measure, you could have relationships with the parameter table which might make things a bit easier.

That's all I can think of for now, but there could well be other methods.

Did you want me to set up a PBIX to demonstrate this? If you had a small PBIX you could share with non-sensitive data with a similar structure to your real one, that would help.

Regards

[kewaynes]

Thanks for the open dialouge, Owen. And the willingness to take a look at my model. How do I attach a pbix? It's saying the file type is not supported.

[OwenAuger]

No problem, glad to help look into this 🙂

I believe the actual attachment function is restricted to super-users.

The best alternative is share a link to any sort of cloud storage (OneDrive, Google Drive etc).

[kewaynes333]

Sharing from my personal account. The one thin I haven't built in yet is the language translation, if that makes a difference for your solution.

Hide Measures Sample Data (1).pbix

Th

[OwenAuger · Accepted solution]

Thanks for that @kewaynes / @kewaynes333 🙂

I've come up with some suggestions in the attached PBIX, including an attempt at translation for the measures. The translation requirement does add some complexity to the calculation group approach, but I think it is do-able.

Oh, apologies for any poor translations in this test model. I used Google translate just for demo purposes.

Here's a summary of what I've tried. I won't list all the DAX code here but you can see it in the attached PBIX (using Tabular Editor for calculation groups).

1. Create a Measure Selection Calculation Group, containing one calculation item for each of the four measures. Each calculation item is simply a reference to the underlying measure.
2. Add these calculated columns to this calculation group:
   1. Order Values: These are the values of UoM[Order] that correspond to each measure, in a pipe-delimited string. I created this by referencing your existing field parameters, but you could do this by any other means.
   2. Measure Portuguese: A Portuguese translation of the Measure column.
3. Create a measure Dummy for Measure CG. This measure just returns BLANK() and is used by the additional calculation group created in the next step.
4. Create a secondcalculation group Filter Measure Selection Calculation Group. The purpose of this calculation group is to filter the items displayed from the calcualtion group from step 1. This contains one calculation item "Filter Measures Based on Order" that checks if the currently filtered calculation item from Measure Selection Calculation Group corresponds to the filtered value of UoM[Order]. It also checks if the selected measure is the one from step 3. If so, it applies the original calculation group to the measure using CALCULATE ( SELECTEDMEASURE(),...), otherwise returns blank which will have the effect of hiding the measure.
5. Create a field parameter called Language for measure language selection, that chooses between the columns 'Measure Selection Calculation Group'[Measure] and 'Measure Selection Calculation Group'[Measure Portuguese]. This contains columns "Language" ("English","Portuguese") and "Measure Tranlsation" ("Measure", "Medida"). This field parameter should be filtered by some means, e.g. RLS or user selection.
6. Finally create a matrix visual:
   1. Place the field parameter Language[Measure Translation] in rows. This will choose between the English or Portuguese measure names in the first calc group.
   2. Place the measure Dummy for Measure CG in values.
   3. Add slicers for Language[Language] and UoM[UoM].
   4. Add a page or visual level filter: Filter Measure Selection Calculation Group[Filter] = "Filter Measures Based on Order".

Here are images of some of the key items:

Measure Selection Calculation Group

Measure Selection Calculation Group

Filter Measure Selection Calculation Group 

Language (field parameter)

Report Page

This was reasonably complex to set up, but I think it could be workable. There is maintenance required whenever measures or languages are added, but that shouldn't be too painful. You may also need a relationship between UoM and Language which you could add.

Hope that helps to some extent!

Regards

[kewaynes]

First of all, I can't believe you wouldn't help me out and get the official Portguese translations, Owen.

Secondly, this is awesome! I really appreciate the time and effort to put this together. I'm going to run this by my team. Overall, I think this would work for us (I mean, it clearly works in the file you sent me), I'm just not sure we would want to go down this route due to the added complexity, given two reasons:

1) We have up to 30 languages we need to support. We'll obviously have to translate everything anyways, but this way may be a little messier, out of necessity.

2) I'm not sure any of the devs on our team have much experience with Tabular Editor. To be honest, I'm pretty novice with it, myself. That said, I was able to adde a language and a measure so I do think I have a fundamental understanding of how this works. I'm sure this is an oversimplification, and possibly even wrong, but I'm thinking fo this approach almost like building a calculation group on top of a field parameter. 

Whether we use it or not, this is the exact outcome I was looking for and it will surely aid myself and my team in the future knowing the possibilities we can accomplish when we dive into Tabular Editor.

Thanks again! 

[OwenAuger]

Hi @kewaynes 

I think a nice way to handle this is to use a calculation group as a measure selector. Alternatively you could create a measure selection measure.

I have attached an example PBIX.

The steps are:

1. Create a calculation group with one calculation item per measure to be included in the visual.
   See the Metric calculation group in this article for example.
2. Place the calculation group column in the Rows field well of the matrix, in the appropriate position.
3. Place any arbitrary measure in the Values field well of the matrix (since the calculation items will replace the measure completely).

From my PBIX, here is a comparison of the original approach (directly using measures) which retains blank measures, and the calculation group approach which hides blank measures (since values from the calculation group column are performing the role of measures).

Does this work for you?

Regards,

[kewaynes]

Thanks for this Owen. This seems like it could work. However, I do have a couple more layers of complexity and am wondering if this approach would still work with them.

1) There is a Unit of Measurement slicer in the report. If the user changes the selection from US to Metric, the report needs to not only change the values in the matrix, but also the measure label. (i.e. - change from 'Engine Oil Temp (°F)' to 'Engine Oil Temp (°C)'. I'm currently using Field Parameters to accomplish this.

2) On top of this, the report needs to support several language translations. These two things on top of each other is making things rather complex. Typically, I would use a translation table for this. Or build onto the Field Parameters where every measure is repeated for each language and then use RLS to filter to the specific measure needed (i.e. - is someone in Brazil is viewing it, show the Portuguese named measure label).

Any thoughts on if calc groups could still support this or do these extra layers cause an issue?