DAX equivalent of sql (Count excluding some filters)

[Anonymous]

Hi Community

What would be the DAX equivalent of the following SQL expression?

SELECT count(distinct userid)
FROM [dbo].[Table]
where Customer = 'C1'

The context is as follows:

• Is a measure in a Power BI report
• The where clause is a report filter
• I want to ignore all page filters

For example

Customer UserId  LogonDate

C1             User1  1/1/19

C1             User1  1/2/19

C1             User2  1/1/19

C1             User3  1/1/19

C2             User1  1/1/19

C2             User2  1/1/19

I want to get as a result 3 independent of the filter on the LogonDate field (page filter)

Thanks in advance for your help

[v-yuta-msft · Accepted solution]

@Anonymous ,

Create a measure using DAX below then drag the measure to a card visual:

Count = CALCULATE(COUNT(Table1[LogonDate]), FILTER(ALL(Table1), Table1[UserId] = "User1"))

 

Community Support Team _ Jimmy Tao

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