How to distinct expression with couple if statements

[Elichka]

Hello, I'm using Direct Query. I have a measure with the couple of if statements and need to distinct the result.

here is an example of my measure: 

Measure = countrows(filter(Table1, IF(ISBLANK(Table1[CUSTID1] ) ,IF(LEN(Table1[CUSTID2])>0,Table1[CUSTID2] || Table1[RECORD_CODE]|| Table1[SOURCE_ID],0),0)))

I tried to use distinct but it return the same result as from the Measure above.

Measure = countrows(DISTINCT(filter(Table1, IF(ISBLANK(Table1[CUSTID1] ) ,IF(LEN(Table1[CUSTID2])>0,Table1[CUSTID2] || Table1[RECORD_CODE]|| Table1[SOURCE_ID],0),0))))

Any help is greatly appreciated!

[v-jialongy-msft]

Hi @Elichka 

Please try the following DAX:

Step 1: Create a Calculated Column
Firstly, create a calculated column in `Table1` that will handle the concatenation under the specified conditions. This simplifies your measure by moving logic out of it.

Unique Identifier =
IF(
ISBLANK(Table1[CUSTID1]) && LEN(Table1[CUSTID2]) > 0,
Table1[CUSTID2] & Table1[RECORD_CODE] & Table1[SOURCE_ID],
BLANK()
)

This column now holds the concatenated identifier where your conditions are met, or BLANK() otherwise.

Step 2: Define the Measure
Now, define a measure that counts the distinct values of this new column. This avoids the complexity of nested `IF` statements within your aggregation function.

Distinct Count Measure =
COUNTROWS(
DISTINCT(
FILTER(
Table1,
NOT ISBLANK(Table1[Unique Identifier])
)
)
)

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Elichka]

Hi Jayleny, thanks for the quick respond!

When tried to implement Unique Identifier got a message "A single value for column Table1[CUSTID2] cannot be determined. This can happen when a measure formula refers to a column that  contains many values without specifying an aggregation such as min, max, count or sum to get a single result. Any idea of how to resolve that? Im just started to learn power bi few month ago, thank you!

[v-jialongy-msft]

Hi @Elichka 

The "Unique Identifier" is a calculate column not a measure.

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Elichka]

Hi Jayleny, unfortunately, I can't create a calculated column as you suggested because we use DirectQuery...is there any other suggestion? Thanks!