Forum Discussion
Grouping Items
Hi Anonymous ,
That really doesn't help me too much, but only because I do not understand your data. You are saying that is different heirarchies in one column, but I can only possibly make out 2, and may be guessing wrong at that.You are going to have to reshape your data so there is a key at the lowest level in your FACT table, then use your DIM table to slice however you want. I created this dummy DIM table:
Then I took your original table, and merged it to level 1, 2, and 3 and expanded the Level one amount. It returned NULL for all but one of those merges, so then I did a "merge columns" on those levels. It now looks like this:
Then you would create a 1-Many relationship between the L1 field of the DIM table to the L1 field of the FACT table. See my PBIX attached.
If you need further help, you are going to have to provide a more comprehensive sample of data and explain the multiple leves and how your business defines those. But you must work this into a Start Schema as I have, or you will continue to hit dead ends in visuals and DAX measures.
Microsoft Guidance on Importance of Star Schema
Super UserAnonymous , do you have a level which can be identified by the number or by Path length or by rank.
Then you can create a color measure and use that
Refer path here
https://radacad.com/dynamic-row-level-security-with-organizational-hierarchy-power-bi
For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415
Color measure you can create like
Color Date = if(FIRSTNONBLANK('Date'[Date],TODAY()) <today(),"lightgreen","red")
Color Date =
var _min =minx(allselected(Date,Date[Year])
return
Switch( true(),
FIRSTNONBLANK('Date'[Year],year(TODAY()))-_min =0 ,"lightgreen",
FIRSTNONBLANK('Date'[Year],year(TODAY()))-_min =0 ,"blue",
"red")
if(FIRSTNONBLANK(Table[Value],"true")= "true","green","red")
How to use
https://radacad.com/dax-and-conditional-formatting-better-together-find-the-biggest-and-smallest-numbers-in-the-column
https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-by-color-values