topic Re: How to get distinct values without null values for a parameter in Report Builder in DAX Commands and Tips

How to get distinct values without null values for a parameter in Report Builder

LotteLaugesen — Tue, 12 Sep 2023 09:47:47 GMT

What I'm tryng to achieve is to get distinct values from 'Dim_Customers'[Customer group name] but I want to remove null values.
Can anybody tell me the correct syntax or how to do it?

This doesn't work 😞
EVALUATE VALUES('Dim_Customers'[Customer group name]),FILTER('Dim_Customers','Dim_Customers'[Customer group name] <> BLANK())

BR
Lotte

Re: How to get distinct values without null values for a parameter in Report Builder

ChiragGarg2512 — Tue, 12 Sep 2023 09:55:45 GMT

Try this DAX:

DistinctCustomerGroup = Distinct(Filter('Dim_Customers', Not IsBlank('Dim_Customers'[Customer group name])))

Re: How to get distinct values without null values for a parameter in Report Builder

LotteLaugesen — Tue, 12 Sep 2023 10:41:26 GMT

Hi ChiragGarg2512

Thank you, but it's inside Query Designer in Report Builder and it doesn't accept that

Re: How to get distinct values without null values for a parameter in Report Builder

ChiragGarg2512 — Wed, 13 Sep 2023 07:01:07 GMT

Sorry for the misunderstanding. Try this:

EVALUATE VALUES('Dim_Customers'[Customer group name]),FILTER('Dim_Customers', (NOT ISBLANK('Dim_Customer'[Customer group name])) && 'Dim_Customer'[Customer group name] <> "")

Re: How to get distinct values without null values for a parameter in Report Builder

LotteLaugesen — Wed, 13 Sep 2023 07:16:08 GMT

Hi ChiragGarg2512

Thank you, but I get this syntax error: