topic Measure with table variable to filter table visual in DAX Commands and Tips

Measure with table variable to filter table visual

rack201 — Tue, 13 Feb 2024 05:05:54 GMT

Hi - I have a table like:

I have a slicer on Staff Name and a table that shows other info in the table for the selected Staff Name

I'm trying to build a measure I can use to filter a second table on the page to show other Staff that have the same Supervisor. The second table is set to have no interaction with the slicer. I've tried some variatons on this theme:

Measure = 

VAR _sup = SELECTEDVALUE(Table[Supervisor Name])
VAR _tab = SELECTCOLUMNS(filter(all(Table), Table[Supervisor Name] = _sup), "Supervisor", Table[Supervisor Name])

RETURN

if(_sup in _tab, 1, 0)

Countrows(_tab) shows the expected number of rows based on the person selected in the slicer, but the Measure seems to be set to 1 for all rows in Table.

Have also tried using CALCULATETABLE with the same result. School me!

Re: Measure with table variable to filter table visual

Anonymous — Wed, 14 Feb 2024 03:24:10 GMT

Hi rack201

I ran the testing as follows.

tables:

The table acts as a slicer.

Create a measure as follows

Measure = VAR _supername = CALCULATE(MAX('Table'[Supervisor Name]), FILTER('Table', [Staff Name] = SELECTEDVALUE('staff'[Staff Name]))) RETURN IF(MAX('Table (2)'[Supervisor Name]) = _supername && MAX('Table (2)'[Staff Name]) <> SELECTEDVALUE(staff[Staff Name]), 1, 0)

Put the measure into the visual-level filters, set up show items when the value is 1.

Is this the result you expect?

If I've misunderstood you, please provide the result you are hoping for. Please remove any sensitive data in advance.

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Measure with table variable to filter table visual

rack201 — Thu, 15 Feb 2024 20:45:35 GMT

Amazing - thank you - that got me going!