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,
Report description:
I have Rooms data, columns: [Room],[Status],[Status Start];[Status End];
I have Date table which has NO Relationship to Rooms Table.
There is a measure to count Rooms in Period per Status (link😞
CountPerPeriod2 = CALCULATE(COUNT(Table2[Room]); FILTER(Table2; ([Status Start] <= LASTDATE('Date'[Date]) && [Status End] > FIRSTDATE('Date'[Date]))))
And there are two visuals:
Problem and requirement:
When the specific date and status is selected in Chart, Table should show ony selected Rooms.
Now Table shows all Rooms for selected Status ignoring date filter:
I guess it is because Date and Rooms has NO Relationship.
But this measure requires NO Relationship.
Is it possible to overcome this issue?
Solved! Go to Solution.
Thank you for the guidance, your comment about Visual level filter helped.
However measure was not correct. This part in IfAvailable measure:
CALCULATE(MAX ( Table2[Status Start] ))
takes MAX of [Status Start], but in those cases then selected day has more than one room available MAX is wrong function to use.
I found simplier solution to this. In table create Visual level filter of the main measure and set it to "is not blank":
Your measure is to calculate count of Vacant/Occupied rooms over a full calendar. It should not have relationship to Date table. So it will always ignore Date[Date] filter. In your scenario, you can create a measure to check if a room is available:
IfAvailable = IF ( CALCULATE ( MAX ( Table[Status_Start] ) ) < MAX ( date[date] ) && CALCULATE ( MAX ( Table[Status_End] ) ) > MAX ( date[date] ), 0, 1 )
Then add a Visual Level filter on your table visual with above measure. Set IfAvailable is 1.
Regards,
Thank you for the guidance, your comment about Visual level filter helped.
However measure was not correct. This part in IfAvailable measure:
CALCULATE(MAX ( Table2[Status Start] ))
takes MAX of [Status Start], but in those cases then selected day has more than one room available MAX is wrong function to use.
I found simplier solution to this. In table create Visual level filter of the main measure and set it to "is not blank":
Thank you for answer.
I created new measure:
IfAvailable = IF ( CALCULATE ( MAX ( Table2[Status Start] ) ) < MAX ( 'Date'[Date] ) && CALCULATE ( MAX ( Table2[Status End] ) ) > MAX ( 'Date'[Date] ); 0; 1 )
Attached Visual filter to table:
After this I don't get desired result:
Table should show only one Row.
@v-sihou-msft can you recheck IfAvailable measure logic is it correct?
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 |
---|---|
107 | |
96 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |