Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
97 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |