Read slicer values, retrieve corresponding table column values and use them to check existence

[kevinfernandez]

Hi all,

I am trying to retrieve a column values based on the selected single/multiple slicer values and use those data to check their existence in the other table.

Let me put this out clearly.

There are two tables i.e., Calendar and Employee data. The calendar has the following columns i.e., fiscal year, fiscal year period, date. Adding the screenshot below.

Calendar table:

The Employee table has Name, Location, Joined Date.

Employee table: 

The slicer in the Power BI dashboard is added with the 'Fiscal year period' column from 'Calendar' table. If the user selects a particular fiscal period or multiple fiscal periods, the corresponding dates from the Calendar table have to be read in a dax calculation and check the existence of those date values in the Joined date column in the table 'Employee'.

For instance, if the user selects 2025 P3 and 2025 P4 from the slicer, then the corresponding dates from 3rd March to 4th May have to be read and check if any of those values exist in the column from other table i.e., in Joined date from Employee table.

Could you please help me with a dax calculation/measures for this scenario?

Any suggestions will be appreciated. Thanks!

@Microsoft @EnterpriseDNA 

[DataNinja777 · Accepted solution]

Hi @kevinfernandez ,

You can achieve this in Power BI using a DAX measure that dynamically filters the Calendar table based on the selected fiscal period(s) in the slicer, retrieves the corresponding dates, and checks for any matches with the Joined date column in the Employee table. The idea is to create a measure that reads the selected values in the slicer, pulls the relevant dates from the calendar, and counts how many of those dates exist in the employee data.

Here is the DAX measure that returns the count of employees who joined during the selected fiscal period(s):

JoinedInSelectedPeriod =
VAR SelectedDates =
    CALCULATETABLE(
        VALUES('Calendar'[Date]),
        'Calendar'
    )
RETURN
    COUNTROWS(
        FILTER(
            'Employee',
            'Employee'[Joined date] IN SelectedDates
        )
    )

This measure calculates the list of dates from the Calendar table based on the slicer selection and checks if those dates are found in the Joined date column of the Employee table. If you prefer to return a simple TRUE/FALSE flag indicating whether at least one employee joined during the selected period, you can use the following version:

AnyJoinInSelectedPeriod =
VAR SelectedDates =
    CALCULATETABLE(
        VALUES('Calendar'[Date]),
        'Calendar'
    )
RETURN
    IF(
        COUNTROWS(
            FILTER(
                'Employee',
                'Employee'[Joined date] IN SelectedDates
            )
        ) > 0,
        TRUE,
        FALSE
    )

These measures work as long as the slicer is based on 'Calendar'[Fiscal Year Period] and both date columns are of the same data type. Let me know if you'd like to display the list of employee names instead.

Best regards,

[kevinfernandez]

Thanks @DataNinja777 
This is what exactly I was looking for. The VALUES dax along with CALCULATETABLE helps in bringing the exact dates while using the slicer and use those dates to see the matches in my calculation. The IN keyword is another catch that helped in my calculation too.

[lbendlin]

Create a data model. Join the calendar date to the "joined" date.  Then add your output columns to a table or orther visual. No code required.

[kevinfernandez]

Thanks @lbendlin but I was looking for the exact dax formulas so that I can get those selected dates and further use them for my calculation.

[RonanCampbell]

I'd come at it in the same way as @lbendlin on the model side. Would also put a date table inbetween your two tables above

[kevinfernandez]

Could you please help me understand how that helps? It would really help me to keep your suggestion as a backup in future. Thanks!