Summarizing records when any one hits a threshold for several measures

[JohnKing]

Hi we have data as pictured below.  Companies have assets. Assets are scored on several measures(Safety, Usability, Compliance, etc).

We need to summarize the companies and asset types that trigger inclusion in a summary output.

We need to create the ouptut below:

We would set threshold values for e.g., Extreme (>90), High (>80) etc.

For a given Company and Asset Type, we identify the measures where at least 1 asset hits the Extreme threshold.

E.g., Beta Co … Buildings hit the threshold for Safety, Instructions and Complaints.

I can see how we might use calculate() to determine the max(score) for each of the measures aggregated at the company and asset type level. Then with some conditional logic we could create a string that concatenates measures names. I'm new to Power BI so I'm not sure that's the right approach and could use help getting the statements right. Thank you all.

PBIX: 

https://1drv.ms/u/s!Ap90MsT-kD-eggWnrAZZtqNmkZ1Z?e=TcMQTA

Additional extensions we are planning (I can create new posts for these if needed or feel free to take a look).

https://1drv.ms/p/s!Ap90MsT-kD-eggcVE6p5jBa4-pw1?e=FkVRe4

[daxer-almighty · Accepted solution]

This is what the model should look like:

Please be aware that there's a many-to-many between Assets[Asset Type] and Config[Asset Type]. But this is how it should be since this method resolves a granularity issue (and that's what many-to-many is for). Secondly, there's a cross-filter between Config and Thresholds. This is OK as well since there's no ambiguity it introduces and nothing else joins to Thresholds. This model allows for very simple DAX as you'll see in the file:

https://1drv.ms/u/s!ApyQEauTSLtOgZZs41DYwAwVokl84g?e=8kTjZs

One last thing... The hidden columns in the tables should not be used by the end user to do any slicing and dicing. Only the ones that are visible should be. Generally, only columns of business meaning in dimension tables should be ever used by the end user. If you don't follow this rule, you're asking for trouble.

Hope this helps.

[JohnKing]

Thank you very much. I made a quick data set since i can't share the actual... we have a scheme much like you're recommening. The threshold table will be a valuable add! I screwed up putting the data in crosstab. Should I accept this answer, since given what i posted it is correct and then repost an update to move on to the summary table display? Thanks again. 

[daxer-almighty]

@JohnKing 

I gave you a link to a shared .pbix file in my previous post. Please download it and take a look at how things have been designed. One word of caution. In You should pay special attention to referential integrity problems. You should not have any. This is very important if you want DAX to calculate correct figures. General advice is not to have any entries in your fact tables that do not have corresponding entries in the relevant dimensions. If you have missing data or something of this kind, each dimension should have a member that will be used in such situations. In the Config table you should store the associations of thresholds with assets and all the other attributes fully, that is, no combination should be omitted. If you don't have a threshold for a certain combination, then just add a member to the Thresholds dimension that will hold a very high number so that no measure can cross it.

Just avoid RI problems at all costs.

[JohnKing]

Thanks so much for the advice. Will heed it!
I'll accept the answer and repost with either your file or something that looks a lot like it.

[daxer-almighty]

Hi @JohnKing 

First and foremost, you should change the data model (here's what models in PBI should look like: Understand star schema and the importance for Power BI - Power BI | Microsoft Docs).

Quick hints. Assets go to their own dimension with only attributes relevant to assets. Companies go to their own dimension with only attributes relevant to companies. Scores go to a fact table which is connected to both dimensions and dimensions are not connected to each other directly, only via the fact table. This is the correct star schema model you should always have. All filters are one-way only and no cross-filtering.

This is the first step. The second would be to make the fact table narrow and long. So, no columns for individual measures (Safety, Usability...) but one column with the type of measure and the other with the value of the measure. You can use the Unpivot operation in Power Query.

The third step would be to define a disconnected table with thresholds and a disconnected table to store the associations between asset types, companies, thresholds and score triggers. Again, this would be a narrow table, each row for a singe combination of the above.

Once you have this, you could then---and then only---proceed with the rest. I don't right now have a time to create a model like this and post a link to it as I'm at work but I'll do it as soon as I get a chance. Meanwhile, you could try to do yourself what I've just described and then re-post a link to a new file. It could save me a bit of work, if you don't mind.

Thanks.

[mahoneypat]

This will be much more straight forward if you unpivot your data first so all your metrics are in the same column.  Then you can filter that column for >90 and use CONCATENATEX to concatenate the metric names.  Otherwise, you'll need to make 5 variables first to filter each of those column for >90 and then return the concatenation of the five columns name that have at least one row above the limit.

Pat