Forum Discussion

Frequent Visitor

3 years ago

Solved

How to use conditionals on filter expression?

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])))

Ashish_Mathur
3 years ago
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)

4 Replies

Ashish_Mathur
Super User
3 years ago
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
  Frequent Visitor
  3 years ago
  Hi Ashish,
  
  It's not a measure. It's part of the data source.
  - Ashish_Mathur
    Super User
    3 years ago
    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)
Anonymous
Not applicable
3 years ago
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.

Related content