#PowerBI folks,
I have 3 tables:
A. Contains scores ranging 0.0-5.0
B. Contains maturity levels with upper & lower score cutoffs for each level (e.g. level 1 is 0-1.5, level 2 is 1.5-2.5, etc)
C. Contains hex codes for formatting scores in each maturity level
I’ve connected B&C via the “maturity level” fields in the modeling tab, but I’m not sure how to tie the score of A to the maturity levels in B.
Any tips are appreciated. Thanks!
@VTDARKSIM combining this into two tables in PowerQuery would make life easier.
Then join tables A+B with C on maturity level.
@Mingagelis @VTDARKSIM Another alternative would be to write a calculated column in Table A using a DAX banding formula referencing Table B (disconnected). Then you can create a relationship between table A and C based on the maturity level.
Matt Allington describes it (see about 1/3 way down this page):
https://exceleratorbi.com.au/banding-in-dax/
For large datasets, calculated columns don't compress as well and can lead to memory problems. For small datasets (<1-10M rows) I doubt it matters. #PowerBI
@QuantumDot2 @Mingagelis this is kind of what I had in mind, even if I had no idea how to do it. The wrinkle is that really I'm trying to determine into which band a calculated measure falls.
Basically the measure averages all of the level 3 scores within a bucket to get an average level 2 score for that bucket. All of the level 2 scores are averaged to get an overall score. The level 2 (bucket) scores are what will dictate formatting for the visual, so trying to see which band they fall into.
@VTDARKSIM @Mingagelis Oh, I (think I) see! If there aren't too many bands, I would probably attempt a base measure to calculate the average (or whatever agg you need), then write a SWITCH(TRUE(), test1, result1, …) measure to return a different value for each result.
You can write a second SWITCH() measure to return coloring hex codes too.
As long as you don't have too many bands, I would guess this approach would work.
This old P3 article stills looks relevant: https://p3adaptive.com/the-diabolical-genius-of-switch-true/
@QuantumDot2 @Mingagelis awesome, thanks for sharing!
I did figured out a way... I had the "Aggregate Score" measure that did what I needed. I created a measure "Webhex" that filtered the formatting table (AB) to the band defined by "Aggregate Score" and returned the corresponding webhex code.
I was so concerned about connecting the tables in the model tab that I didn't even consider doing it this way.
It's probably not the cleanest way to do it, but it works for now!
@Mingagelis aye I feel dumb for not having merged A&B in Power Query. But, I hadn't done that yet so I learned something new today!
So now I have AB and C. I should have clarified, C has a column of scores but a measure that aggregates (avg) scores upward in a hierarchy. The scores at the 2nd level need the formatting info from AB.
Since C has a measure (decimal) & AB has range boundaries, would it make sense to create another measure in C to decide which range the avg measure fits into?
Chris Geoghooligan ✊
Mindaugas
Andy Napper
