Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
JustinDoh1
Post Prodigy
Post Prodigy

Show value only if there is one value

I am sharing a Pbix file here at this Googld drive.

I am trying to have an empty value if there is more than one value on these areas (4 box with orange colors).

So, on the bottom image, value for "Max" column shows "85+" because the measure Max does not limit anything that has more than one value.

JustinDoh1_4-1626976435494.png

JustinDoh1_2-1626976148932.png

I previously used "SelectedValue", but the issue happens because on some cells, the value does not show (like box with green color) because there are more than 1 output.

JustinDoh1_5-1626976645918.png

 

I went ahead and modified like this, but now the issue is Hasonevalue('Table'[Age Band]) filtered this one because there are two outputs for Feb 2020 for 'Brave, Heart'.

JustinDoh1_0-1626977393920.png

Is there anyway to fix this calculated column so that when somebody reaches, let's say 64, it does not populates both "55-64" and "65 -74"?

JustinDoh1_0-1626977582895.png

 

How do I modify the DAX code of "Max" so that I could only show value if there is only one value & DAX code of "Age Band" so that it only populates one value if somebody reaches exactly certain age (this case of age 64 as "55-64")?

 

 

 

 

1 ACCEPTED SOLUTION
Mohammad_Refaei
Solution Specialist
Solution Specialist

You should use ISINSCOPE not HASONEVALUE which will fix the issue since you don't want to show any values if there is any grouping.

 

Max = IF ( ISINSCOPE ( 'Table'[Full Name] )MAX ( 'Table'[Age Band] ) )

 

Regarding the selectedvalue measure, you may follow the same technique.

Selectedvalue =
IF (
    ISINSCOPE ( 'Table'[Full Name] ),
    TOPN ( 1VALUES ( 'Table'[Age Band] ) )
)

m_refaei_0-1627014487911.png

 

View solution in original post

5 REPLIES 5
Mohammad_Refaei
Solution Specialist
Solution Specialist

You should use ISINSCOPE not HASONEVALUE which will fix the issue since you don't want to show any values if there is any grouping.

 

Max = IF ( ISINSCOPE ( 'Table'[Full Name] )MAX ( 'Table'[Age Band] ) )

 

Regarding the selectedvalue measure, you may follow the same technique.

Selectedvalue =
IF (
    ISINSCOPE ( 'Table'[Full Name] ),
    TOPN ( 1VALUES ( 'Table'[Age Band] ) )
)

m_refaei_0-1627014487911.png

 

@Mohammad_Refaei Thank you so much for your help. I really appreciate it. Now, I know that I need to learn about ISINSCOPE. I have a question for you. You have illustrated two different formulas, but both end with same results. I guess either case (Max or TOPN ( 1VALUES)), both are logically same? When do we use Max and when do we use TOPN? If you can provide, that is great, otherwise, I will just Google them. Thanks!

Most welcome @JustinDoh1 ... I didn't use MAX since the age band is text not numerical value, so I selected the first available text value.

@Mohammad_Refaei Thanks. So, is it just your preference or would it cause error if I use MAX?

MAX also will work, it is just a matter of preference. Remember that we already have only one value in this case.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Kudoed Authors