Count unique for each individual day

[domtrump]

I have a power pivot report that lists all the occupied room numbers for a hotel for each day. More than one person can share a room, so room numbers can be duplicated in any given day. I can use DISTINCTCOUNT in a measure and this works fine if I want to look at each individual day. However, when I want to do my count for the entire month, I of course just get a unique count of rooms for the month. I need a measure that will count the unique room numbers for each DAY and then all up all of the days to give me a MONTH total. Can someone help me out with the DAX syntax? Thanks.


My data looks like this:

Excel Workbook
	B	C	D	E	F
3	Date	Month	Rm Number	*	Unique Rooms
4	1-Jan	1	1205	*	*
5	1-Jan	1	1205	*	*
6	1-Jan	1	1310	*	2
7	2-Jan	1	1205	*	*
8	2-Jan	1	1310	*	2
9		*	*	*	*
10		*	*	*	*
11	January	1	*	*	4
12	*	*	*	*	*
Sheet1

In this example, there were two unique rooms for each day resulting in a monthly total of 4 rooms.

 

[Ozeroth]

I think either of these will do what you want, adding up the number of distinct Rm Numbers on each date. I think the first one should perform better.
(I've just put dummy table names)

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

=
COUNTROWS ( SUMMARIZE ( FactTable, [COLOR=#ff0000][B]DateTable[/B][/COLOR][Date], [B][COLOR=#ff0000]FactTable[/COLOR][/B][Rm Number] ) )

 

[domtrump]

That is perfect! Thank you so much.