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,
I am having trouble with what I thought was a really easy measure.
I hav a DimDate dimension and FactEvents fact table joined on date key(not date). I also have a DimLocations Dimension join to the FactEvents.
FactEvents doesn't have a date in it just the key. I am trying to find the first ever event for each location. I can find the first date_key ok but I am having a devil of a job of getting the actual first date using the DimDate dimension. I want the first event regardless of any date filters applied.
First Event =
CALCULATE (
MIN ( FactEvents[event_date_key] ),
ALL ( FactEvents ),
FILTER (
DimLocationDoorLock,
DimLocationDoorLock[LocationDoorLockKey]
= MAX ( DimLocationDoorLock[LocationDoorLockKey] )
)
)
works fine but I cannot get to a min of DimDate date without getting the date onto the fact table. Is there a cunning way around this?
Solved! Go to Solution.
@gooranga1 , Try like
First Event =
CALCULATE (
MINX(all(FactEvents), related('Date'[Date]))
FILTER (
DimLocationDoorLock,
DimLocationDoorLock[LocationDoorLockKey]
= MAX ( DimLocationDoorLock[LocationDoorLockKey] )
)
)
Two dimesion do not join, so we have to go via fact
hi @amitchandak yes thanks I was floundering about with something similar but it still doesn't seem to filter on the location bit and gives one date for all. So added the measure above. I do have the date field added now but I would like to know how to do it without as well.
**********************************************************
**********************************************************
Added Allselected and it has done the trick! thanks
@gooranga1 , Try like
First Event =
CALCULATE (
MINX(all(FactEvents), related('Date'[Date]))
FILTER (
DimLocationDoorLock,
DimLocationDoorLock[LocationDoorLockKey]
= MAX ( DimLocationDoorLock[LocationDoorLockKey] )
)
)
Two dimesion do not join, so we have to go via fact
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
88 | |
63 |