Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I want to make a matrix table based on a hierarchy with 4 levels. The columns contain two different measures:
- Hours: this measure can be broken down to the lowest level of the hierarchy
- Hours planned: this measure can only be broken down to the first two levels of the hierarchy
Now my table looks like this:
Hours | Hours planned | |
Agreement A | 25 | 30 |
Budget Name A | 15 | 20 |
Article A | 20 | |
Article B | 20 | |
Article C | 8 | 20 |
… | 20 | |
Article O | 7 | 20 |
Employee A | 20 | |
Employee B | 7 | 20 |
Employee C | 20 |
For the levels Article and Employee the 20 Planned hours can't be broken down and for every existing Article and Employee the table is showing 20.
I would like to not show this 20 for these two levels of the hierarchy, resulting in this:
Hours | Hours planned | |
Agreement A | 25 | 30 |
Budget Name A | 15 | 20 |
Article C | 8 | |
Article O | 7 | |
Employee B | 7 |
Because there is no number of Hours plannend for the lowest to levels, only the rows with a value for Hours are shown.
Which measure could I use to obtain this result?
Solved! Go to Solution.
I figured it out by turning the DAX formula the other way around:
I figured it out by turning the DAX formula the other way around:
@Ilse_ScpDt ,I think isinscope can help
if(isinscope(Table[Agreement]) || isinscope(Table[Budget Name]) , [Hours planned], blank())
https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/
or
https://powerpivotpro.com/2013/03/hasonevalue-vs-isfiltered-vs-hasonefilter/
Thanks for the quick reply.
I already tried ISINSCOPE, but it isn't working. Still the value is shown on every level of the hierarchy.
The other options are also not working. They all only work the other way around.
So if you only want to show the two lowest levels it is working. But I would like to show the two highest levels. Thing with these DAX formulas is that the lower levels I don't want to show are "in scope" of Agreement and Budget Name.
Is there an other solution?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |