Calculate distinct count with different aggregations

[pgarcia]

Hi all!!

Currently, I'm working for a hotel chain and I want to do a report that show Customers who repeat visits only different hotels (not repeat  same hotel) with a date slicer.

For Example. I have the next source:

Id_Customer        Date           Hotel

      1                 20171101         C

      1                 20180507         A

      1                 20181125         B

      2                 20180312         B

      2                 20180701         C

     3                  20181201         A

In the date slicer in this case I'd have selected: 20180101 - 20181231

The result would be a table with:

Hotel     Customer Repeat

   A                   1  (Customer 3 has only visited once, it must'nt be counted)

   B                   2

   C                   1  (Customer 1 is out of range date selected)

I have the first step:

But I don't know to apply this calculations to count hotels.

In SQL, I think that the query would be the next: 

SELECT * FROM
( select A.ID_CUSTOMER, A.HOTEL, A.DATE , ( SELECT ID_CUSTOMER FROM TABLE WHERE DATE>=20180101 AND DATE<=20181231 and ID_CUSTOMER= A.ID_CUSTOMER group by ID_CUSTOMER having count(DISTINCT HOTEL) > 1 ) as ID_CUSTOMER_REP
from
TABLE as A WHERE DATE>=20180101 AND DATE<=20181231 ) AS C where ID_CUSTOMER_REP is not null

The date slicer should filter the date in C (SQL Query ) and in ID_Customer_rep.

Many thanks.

Best regards

[v-juanli-msft · Accepted solution]

Hi @pgarcia

Create a new table called "date table", don't connect this table to "sheet" table.

Date table = CALENDARAUTO()

Then create measures in the "Sheet" table

add the [date] column from "Date table" in the slicer.

min-selected date = MIN('Date table'[Date])

max-seletced date = MAX('Date table'[Date])


discount-hotels =
CALCULATE (
    DISTINCTCOUNT ( Sheet[Hotel] ),
    FILTER (
        ALLEXCEPT ( Sheet, Sheet[Id_Customer] ),
        Sheet[Date] <= [max-seletced date]
            && Sheet[Date] >= [min-selected date]
    )
)

cust-count =
CALCULATE (
    DISTINCTCOUNT ( Sheet[Id_Customer] ),
    FILTER (
        ALLEXCEPT ( Sheet, Sheet[Hotel] ),
        Sheet[Date] >= [min-selected date]
            && Sheet[Date] <= [max-seletced date]
            && [discount-hotels] <> 1
    )
)

Best Regards

Maggie