Finding first event

[gooranga1]

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?

[gooranga1]

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.

measure brings back all dates

First Client Event 1 =

CALCULATE (

MINX ( ALL ( FactEvents ), RELATED ( 'DimDate'[Date] ) ),

FILTER (

DimLocationDoorLock,

DimLocationDoorLock[LocationDoorLockKey]

= MAX ( DimLocationDoorLock[LocationDoorLockKey] )

)

)

**********************************************************

**********************************************************

Added Allselected and it has done the trick! thanks

First Client Event 1 =

CALCULATE (

MINX ( ALLSELECTED ( FactEvents ), RELATED ( 'DimDate'[Date] ) ),

FILTER (

(DimLocationDoorLock),

DimLocationDoorLock[LocationDoorLockKey]

= MAX ( DimLocationDoorLock[LocationDoorLockKey] )

)

)

[amitchandak · Accepted 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