DAX CountDistinct with Filter

[domtrump]

I kind of thought this would be pretty easy (and maybe it is) but every example I've come across in searching yields either an error or unexpected results. I have the following measure:

Gamer_Cnt:=calculate(countrows(HDET01),filter(HDET01,[GAMER]="GAMING"))

I need to convert this into a measure that gives me the DISTINCTCOUNT (based on a field called HDET01[FFRMNO]) for the records in a table that is filtered on [GAMER]="GAMING".

If I just create a DISTINCT COUNT calculation and then create a slicer with the GAMING column, I can get he desired results, so I'm guessing there must be a way to do this in a measure.

Help appreciated.

-Dom

 

[theBardd]

Try

=CALCULATE( COUNTROWS( DISTINCT( HDET01[FFRMNO] )), HDET01[GAMER]="GAMING")

 

[domtrump]

Great - that worked! Follow up:

I have another measure that gives me the unique count of room numbers for a given day:

=
SUMX (
    VALUES ( [COLOR=#ff0000][B]DateTable[/B][/COLOR][Date] ),
    CALCULATE ( DISTINCTCOUNT ( [COLOR=#ff0000][B]FactTable[/B][/COLOR][Rm Number] ) )
)

How can I adapt this calculation so that it also has a Criteria of
HDET01[GAMER]="GAMING"?

Thanks.

 

[domtrump]

I tried the formula below, but it gave me way more records than were even in the table! Any suggestions?

TestUnq_Rms_Game:=SUMX (
    VALUES ( DT_01[Date] ),
   calculate(DISTINCTCOUNT([FFRMNO]),filter(HDET01,[GAMER]="GAMING")))

 

[akice]

If you put the DT_01[Date] field on the row of the Pivot Table, theBardd formula gives you the distinct count of room #'s per day for "GAMING". What else are you looking for?

 

[domtrump]

My bad, I should have referenced the original post. I have MONTHS in the pt - not days. If I use the calc above, it just gives me the total number of rooms in the hotel. I need the unique number for each day - summed up for the month. (sorry again)

 

[akice]

ok first off, as a matter of best practice in your function code the naming convention is to fully qualify column names ( use HDET01[GAMER], not just [GAMER]) and just use bracket notation only for measures [TestUng_Rms_Game]. It makes the code much easier to read by yourself and others trying to help.

Second, the code you posted above should work for what you want then. Here is a slightly shorter version but functionally (almost) the same:

TestUnq_Rms_Game:=SUMX ( DT_01[Date] , CALCULATE(DISTINCTCOUNT( HDET01[FFRMNO] ), HDET01[GAMER] = "GAMING")

So when you say "but it gave me way more records than were even in the table!" not sure what you mean unless it is showing months without any data? Do you have any other fields in the rows or columns? DT_01 is related to HDET01 via the Date field right?

 

[domtrump]

With a minor adjustment, it worked great!

TestUnq_Rms_Game2:=SUMX ( [B]DT_01[/B] , CALCULATE(DISTINCTCOUNT( HDET01[FFRMNO] ), HDET01[GAMER] = "GAMING")[B])[/B]

I don't understand why the first version didn't work either. But this formula returns 234,055 records (without any filters) - the first one returned 1,377,723. ??

Thanks for your help (and advice)!

-Dom

 

[akice]

What column in DT_01 are you using to link DT_01 and HDET01?

 

[domtrump]

Date=Date