Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi, I have an issue with the Matrix Table. I'm preparing a report based on Azure Portal exports and I wanted to calculate the Reserved Instances values on my own. The issue occurs when I'm trying to expand the row levels of the matrix table, please see screenshot below:
The calculation is right only for the highest level of hierarchy, however, when it goes deeper, it doesn't show any values (blue circles on the screenshot).
1 Year MonthlyBenefit =
VAR _Benefit =
SUM ( 'Usage Details'[Cost] ) - 'Usage Details'[1Y Monthly_Cost]
RETURN
SWITCH (
TRUE (),
ISINSCOPE ( 'Usage Details'[ResourceName]),
IF (
AND ( _Benefit > 0, 'Usage Details'[1Y Monthly_Cost] > 0 ),
_Benefit,
BLANK ()
),
BLANK ()
)
Above, you can find the calculation which I'm currently using. I've been trying to add some more ISINSCOPE elements, but unfortunately the results were wrong / not shown at all.
Names of all 3 levels are: SubscriptionName, ResourceGroup and ResourceName.
Can someone give me a hint, how can I show the results for all levels of the hierarchy?
Thank you,
BR,
Marco
Hi @Marco2903 ,
Please test the calculation for each level of the hierarchy separately to determine where the problem occurs.
Test if the condition after the if is False, causing it to consistently return a blank.
Best Regards,
Gao
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data in the Power BI Forum -- China Power BI User Group
Tried to use another ISINSCOPE instances, without success. Still struggling to get the right results. Does anyone have any idea how to fix it?
Hi @Marco2903
The other levels are blank because of the ISINSCOPE limiting your calculation to the Resource Name level.
IF you simply want to aggregate the amounts on the higher levels of the hierarchy, then in theory you could just exclude the ISINSCOPE from the RETURN statement and it should work.
1 Year MonthlyBenefit =
VAR _Benefit =
SUM ( 'Usage Details'[Cost] ) - 'Usage Details'[1Y Monthly_Cost]
RETURN
IF (
AND ( _Benefit > 0, 'Usage Details'[1Y Monthly_Cost] > 0 ),
_Benefit,
BLANK ()
)
Can you let me know if this works, and if not, what the issue is, and what the expected result should have looked like?
Br,
Daniel
Proud to be a Super User! | |
Tried to implement your solution, unfortunately, nothing changes. I named it "MonthlyCost Check" Please take a look on the screenshot below:
The result is being shown only for Resource Name level (it's mentioned in the DAX formula, however, when I'm trying to replace Resource Name with any other column name, nothing changes).
My expected solution would look like that:
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
92 | |
86 | |
85 | |
66 | |
49 |
User | Count |
---|---|
140 | |
113 | |
104 | |
64 | |
60 |