How to loop through a list and use the items as filter values

[Anonymous]

Hello all

I am trying to create a calculated table that contains valid employees based on a selection of dates from a slicer. Unfortunately, I don't know how to iterate through the date list. I am using the Dates and Employees tables as shown below. The first table shows three selected dates as in a slicer. The third table shows the expected table containing all employees where the selected dates are between Validfrom and Validto.

Date Table:

Employee Table:

Expected calculated Table:

I have tried something like this:

CalculatedTable =
VAR Dates =
    ALLSELECTED ( Dates[Date] )
RETURN
    CALCULATETABLE (
        Employee;
        Dates[Date] >= Employee[ValidFrom]
            && Dates[Date] <= Employee[Validto]
    )

Can you please help how to do this in DAX. Thanks a lot

[johnt75]

Calculated tables are only calculated during data load or refresh, so they don't pay any attention to slicers. If you want something to react to slicers dynamically you will need to use a measure instead.

You can store a table in a variable inside a measure, and then use that as a filter for any calculations you need to perform.

[Anonymous]

Hi  john75,

Could you provide a sample of what that would look like?  Thanks!

[johnt75]

Dummy Measure =
VAR DatesAndEmployees =
    GENERATE (
        ALLSELECTED ( 'Date'[Date] ),
        SELECTCOLUMNS (
            FILTER (
                Employees,
                Employees[Valid from] <= 'Date'[Date]
                    && (
                        ISBLANK ( Employees[Valid to] )
                            || Employees[Valid to] >= 'Date'[Date]
                    )
            ),
            Employees[Employee ID],
            Employees[Name]
        )
    )
VAR Result =
    CALCULATE ( DISTINCTCOUNT ( Employees[Employee ID] ), DatesAndEmployees )
RETURN
    Result

The first variable generates the table you wanted to build. Not sure what you wanted to use it for, but this example counts the number of employees who were valid on any of the given dates.

[Anonymous]

Hi @johnt75 
Thanks for your reply. 
Is it possible to return the filtered table "DatesAndEmployees" and use it as a table in a visual (table, matrix) in Power BI?

[johnt75 · Accepted solution]

No, I'm afraid not. If you wanted to list the employees you would need to create a measure like

Dummy Measure =
VAR DatesAndEmployees =
    GENERATE (
        ALLSELECTED ( 'Date'[Date] ),
        SELECTCOLUMNS (
            FILTER (
                Employees,
                Employees[Valid from] <= 'Date'[Date]
                    && (
                        ISBLANK ( Employees[Valid to] )
                            || Employees[Valid to] >= 'Date'[Date]
                    )
            ),
            Employees[Employee ID],
            Employees[Name]
        )
    )
VAR Result =
    IF (
        SELECTEDVALUE ( Employees[Employee ID] )
            IN CALCULATE (
                VALUES ( Employees[Employee ID] ),
                KEEPFILTERS ( DatesAndEmployees )
            ),
        1
    )
RETURN
    Result

and use that as a filter on the visual to only show when the value is 1.