Forum Discussion

Greg_Deckler's avatar
Greg_Deckler
Community Champion
9 years ago

Super Groups Extended Question

This is an extension question to my blog on Groups and Super Groups. Vvelarde perhaps you can bail me out yet again!

 

Super Groups blog article:

https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

 

So, in the original model with columns, in the SuperGroups table, I could create a column like:

 

Maturity = IF([MinScore]<50,"Bad","Good")

I could then create a column chart that showed me the count of "Bad" versus "Good" with "Maturiy" as the Axis and "Count of SuperGroup" as the Values

 

Switching this up to a measure however, I can create a similar measure for maturity. However, I cannot get the count of "Bad" and "Good" in the same way because I cannot use the Maturity as a Measure for the Axis.

 

Do you think I could use the disconnected table trick as in my last question about using measures in rows to solve this one too?

Need Help

2 Replies

  • MattAllington's avatar
    MattAllington
    Community Champion
    9 years ago

    Maybe off topic, but what is wrong with your current column design?  This column would compress really well and take negligible space. 

    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion
      9 years ago

      Hey MattAllington, hope you are well! You have to read through the article, the column approach causes problems when you store multiple "things" in your main fact table and try to use RLS or slicers to select which set of "things" you want to look at. But, I think I solved it.

       

      Here is what I did:

       

      Created a disconnected table called SuperGroupMaturity:

       

      Maturity

      Bad

      Good

       

      Created the following measures:

       

      Bad = COUNTX(FILTER(SUMMARIZE ( SuperGroups, SuperGroups[SuperGroup], "Maturity",[MinScoreMeasure] ),[Maturity]<50),[Maturity])

      Good = COUNTX(FILTER(SUMMARIZE ( SuperGroups, SuperGroups[SuperGroup], "Maturity",[MinScoreMeasure] ),[Maturity]>50),[Maturity])

      Then, created this measure:

       

      Measures to Show = 
IF(
     HASONEVALUE(SuperGroupMaturity[Maturity]),
     SWITCH(
          VALUES(SuperGroupMaturity[Maturity]),
          "Bad",[Bad],
          "Good",[Good]
	 )
)

      Then I could create a column chart with an Axis of "Maturity" from my SuperGroupMaturity table and "Measures to Show" as my Value.

       

      I'm pretty sure this is solid and works. If anyone wants to confirm, I'll have to add this as an addendum to the blog post.