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.
Hello I have this measure below
it gives me this output. As you can see North Sydney Strorage below is giving me BLANK.
What I would like is for any blank values as such, I would like to capture the value of the above hierarchy, in this case the value 1.00 from 343-Nova Scotia North.
am I providing all the details needed for you to help me?
Solved! Go to Solution.
Hi, @salman_ali
You may try the following measure to see if it helps.
Result =
IF (
ISINSCOPE ( 'Table'[Level4] ),
SUM ( 'Table'[Val] ),
IF (
ISINSCOPE ( 'Table'[Level3] ),
1,
IF (
ISINSCOPE ( 'Table'[Level2] )
&& MAX ( 'Table'[Level2] ) = "AT1",
1.16,
IF ( ISINSCOPE ( 'Table'[Level1] ) && MAX ( 'Table'[Level1] ) = "EST", 0.68 )
)
)
)
If it doesn't work, please show us some sample data with OneDrive for business. Do mask sensitive data before uploading.
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @salman_ali
Based on your description, I assume that you want to do different calculations for different level. I created data to reproduce your scenario. The pbix file is attached in the end.
Table:
You may create a measure as below.
Result =
IF(
ISINSCOPE('Table'[Level4]),
SUM('Table'[Val]),
IF(
ISINSCOPE('Table'[Level3])&&MAX('Table'[Level3])="343-Nova Scotia North",
1,
IF(
ISINSCOPE('Table'[Level2])&&MAX('Table'[Level2])="AT1",
1.16,
IF(
ISINSCOPE('Table'[Level1])&&MAX('Table'[Level1])="EST",
0.68
)
)
)
)
Result:
For further information about 'isinscope', please refer to the following document.
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@v-alq-msft Hi I see that your measure will be able to work, however is it possible for you to give me a more generic measure as opposed to have "343-Nova Scotia North" in measure? since I am working with varios other markets other than "343-Nova Scotia North" as well
Hi, @salman_ali
You may try the following measure to see if it helps.
Result =
IF (
ISINSCOPE ( 'Table'[Level4] ),
SUM ( 'Table'[Val] ),
IF (
ISINSCOPE ( 'Table'[Level3] ),
1,
IF (
ISINSCOPE ( 'Table'[Level2] )
&& MAX ( 'Table'[Level2] ) = "AT1",
1.16,
IF ( ISINSCOPE ( 'Table'[Level1] ) && MAX ( 'Table'[Level1] ) = "EST", 0.68 )
)
)
)
If it doesn't work, please show us some sample data with OneDrive for business. Do mask sensitive data before uploading.
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 |
---|---|
112 | |
99 | |
73 | |
72 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |