cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## 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

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: Calculate distinct count with different aggregations

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

Community Support Team

## Re: Calculate distinct count with different aggregations

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