topic Re: Show value only if there is one value in DAX Commands and Tips

Show value only if there is one value

JustinDoh1 — Thu, 22 Jul 2021 18:48:19 GMT

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.

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.

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'.

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"?

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")?

Re: Show value only if there is one value

Mohammad_Refaei — Fri, 23 Jul 2021 04:28:28 GMT

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 ( 1, VALUES ( 'Table'[Age Band] ) )
)

Re: Show value only if there is one value

JustinDoh1 — Fri, 23 Jul 2021 17:19:48 GMT

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 ( 1, VALUES)), 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!

Re: Show value only if there is one value

Mohammad_Refaei — Fri, 23 Jul 2021 23:40:37 GMT

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.

Re: Show value only if there is one value

JustinDoh1 — Fri, 23 Jul 2021 23:57:22 GMT

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

Re: Show value only if there is one value

Mohammad_Refaei — Sat, 24 Jul 2021 00:09:16 GMT

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