topic Re: How to loop through a list and use the items as filter values in DAX Commands and Tips

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

Anonymous — Thu, 15 Jun 2023 11:16:06 GMT

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

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

johnt75 — Thu, 15 Jun 2023 13:32:31 GMT

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.

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

Anonymous — Thu, 15 Jun 2023 14:30:30 GMT

Hi john75,

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

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

johnt75 — Thu, 15 Jun 2023 15:02:28 GMT

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.

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

Anonymous — Thu, 15 Jun 2023 15:41:21 GMT

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?

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

johnt75 — Thu, 15 Jun 2023 16:00:14 GMT

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.