Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
I manage animal movements in buildings.
I get this table MOVEMENT : ID_movement, entry_date (in the building), exit_date (of the building), number of animals, building_id
When I choose one date, I want to get empty buildings. (it means building with no animal, with no current movement).
What is the solution ?
Thanks for your help
Jérôme
Solved! Go to Solution.
Hi @jsteffe ,
According to your description, here's my solution.
Create a measure:
Measure =
IF (
MAX ( 'Table'[start date] ) <= SELECTEDVALUE ( 'Date'[Date] )
&& COALESCE ( MAX ( 'Table'[end date] ), TODAY () )
>= SELECTEDVALUE ( 'Date'[Date] ),
1,
0
)
Put the measure in the visual level filter and select "is 1".
After apply filter, get the correct result:
I attach my sample below for your reference.
Best regards,
Community Support Team_yanjiang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @jsteffe ,
According to your description, here's my solution.
Create a measure:
Measure =
IF (
MAX ( 'Table'[start date] ) <= SELECTEDVALUE ( 'Date'[Date] )
&& COALESCE ( MAX ( 'Table'[end date] ), TODAY () )
>= SELECTEDVALUE ( 'Date'[Date] ),
1,
0
)
Put the measure in the visual level filter and select "is 1".
After apply filter, get the correct result:
I attach my sample below for your reference.
Best regards,
Community Support Team_yanjiang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, I didn´t get the question right, but it seams, that you would need to use IF here.
id_movement | Building_ID | start date | end date | number of animals |
1 | A | 01/01/2023 | 30/03/2023 | 150 |
2 | A | 15/02/2023 | 25/05/2023 | 120 |
3 | B | 01/01/2023 | 10/01/2023 | 10 |
4 | B | 02/02/2023 | 16/04/2023 | 15 |
5 | C | 01/04/2023 | 25 |
With this date, if I choose 20/04/2023, I get 2 buildings that are not empty : A and C.
How to get these 2 buildings ?