topic Re: Distinct values of a column with filttering by another column in DAX Commands and Tips

Distinct values of a column with filttering by another column

CAPSTALKER — Thu, 27 Aug 2020 10:10:34 GMT

Hi all,

I'm new to Power BI and DAX, so thanks in advance for helping.

I have a table named RUM:

I need to get number of distinct dates for each email address while at same time filttering some of emails.

From the table above result should be:

I have following DAX but I'm not totally sure if it gives the right kind of result.

unique dates =

CALCULATE(

DISTINCTCOUNT(RUM[date]),

FILTER(

RUM,

RUM[email] <> "dummy@dotcom.com" &&

RUM[email] <> "kim@dot.com"

)

I know there are multiple ways to do this and would prefer the simplest. Any help and comments will be highly appreciated.

Greg_Deckler — Thu, 27 Aug 2020 11:02:40 GMT

I do not see any issue with what you have in your calculation. Another way to do it is:

unique dates = COUNTROWS( DISTINCT( SELECTCOLUMNS( FILTER(RUM,RUM[email]<>"dummy@dotcom.com" && RUM[email]<>"kim@dot.com"), "date",[date] ) ) )

amitchandak — Thu, 27 Aug 2020 11:09:22 GMT

CAPSTALKER , Try like

unique dates = CALCULATE( DISTINCTCOUNT(RUM[date]), FILTER(RUM, not( RUM[email] in {"dummy@dotcom.com" , "kim@dot.com" })))