Forum Discussion

pgarcia's avatar
pgarcia
Frequent Visitor
7 years ago
Solved

Calculate distinct count with different aggregations

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

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    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

1 Reply

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    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