How to use conditionals on filter expression?

[samsa18]

Hi all,

I'm trying to count the unique ids based on the 90 day amount. However, one problem I ran to is that it would count the 120 day amount, which is not what I want and highlighted in blue.  I'm still new to BI, so I'm not sure what syntax/formatting to use to tell my function to ignore those columns. 

Here's the measure I used:  

Unique Count Measure = CALCULATE(DISTINCTCOUNT('PSJH DQ 9-13-2022'[ID]), FILTER('PSJH DQ 9-13-2022',('PSJH DQ 9-13-2022'[Day 90 Amount])))

[v-yangliu-msft]

Hi  @samsa18 ,

You can try adding ALL() or ALLSELECT() to the formula:

Unique Count Measure=
CALCULATE(DISTINCTCOUNT('PSJH DQ 9-13-2022'[ID]),FILTER(ALL('PSJH DQ 9-13-2022'),'PSJH DQ 9-13-2022'[Day 90 Amount]))

Unique Count Measure =
CALCULATE(DISTINCTCOUNT('PSJH DQ 9-13-2022'[ID]),FILTER(ALLSELECTED('PSJH DQ 9-13-2022'),'PSJH DQ 9-13-2022'[Day 90 Amount]))

Refer to:

ALL function (DAX) - DAX | Microsoft Learn

ALLSELECTED function (DAX) - DAX | Microsoft Learn

Best Regards,

Liu Yang

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

[Ashish_Mathur]

Hi,

If Day 90 amonut is already a measure that you have written, then write this measure

Distinct count = countrows(filter(values('PSJH DQ 9-13-2022'[ID]),[Day 90 Amount]>0))

Hope this helps.

[samsa18]

Hi Ashish,

It's not a measure. It's part of the data source.

[Ashish_Mathur · Accepted solution]

Hi,

Try this

=calculate(distinctcount('PSJH DQ 9-13-2022'[ID]),'PSJH DQ 9-13-2022'[Day 90 Amount]>0&&'PSJH DQ 9-13-2022'[Day 120 Amount]=0)