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.
Hello,
I'm utilizing the open tickets solution to solve hotel occupancy, from https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/td-p/409364, but there are some reservations that are shared. Each reservation has it's own reservation number or ticket number, but they share a hotel room and share number. Therefore using countrows overstates the number of room nights by double counting shares. I'm attempting to get a distinct count of the share numbers day by day that can be summarized.
Using distinctcount('Table'[Share]) gives the correct number of shares per day, but of course doesn't work for mulitple days. Using
Tickets Open = VAR tmpTickets = ADDCOLUMNS('Tickets',"Effective Date",IF(ISBLANK([Closed Date]),TODAY(),[Closed Date])) VAR tmpTable = SELECTCOLUMNS( FILTER( GENERATE( tmpTickets, 'Calendar' ), [Date] >= [Opened Date] && [Date] <= [Effective Date] ), "ID",[Ticket Num], "Date",[Date] ) VAR tmpTable1 = GROUPBY(tmpTable,[ID],"Count",COUNTX(CURRENTGROUP(),[Date])) RETURN COUNTROWS(tmpTable1)
But all of my results have been no where accurate.
Here is a link to my test data.
Thank you in advance for any assistance or direction.
Seth
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 |
---|---|
49 | |
25 | |
20 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |