DAX functions break the slicer filter?

[alvinwiyanto91]

Hi All,

I have data model as per below:

Data Model - All in red are the keys (date type)

Then here's my report view:

Filtered for Nov-2021

I'm trying to use below code (in Measure) to get the staffID from Log table and check if they appear on License table:

Lookup Test =
IF (
    SELECTEDVALUE ( 'VT Log Final'[VT Staff.StaffId] )
        IN VALUES ( 'VT License Usage'[staffID] ),
    1,
    0
)

The measure seems break the filter from the date slicer as it shows the October on the Log table visual (Tracked Usage) and it filter to Nov again if I take the measure out. The table also contain hierarchy by Date -> StaffID (Apologise it's not on the picture)

My best guess is due to the look up by IN function on the License table's Staff ID and it bring all staff which in the look up but from the all time (not only Nov-21)? I don't really understand why 😞

Thanks guys

[MFelix]

Hi @alvinwiyanto91 ,

In this case and since you want to pick up the staff ID is to create antoher dimension table that relates to the other two by the staff id then you can use it in a visualization and check if the values are in both tables.