Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
rack201
Frequent Visitor

Measure with table variable to filter table visual

Hi - I have a table like:

 

Capture.PNG

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!

 

1 ACCEPTED SOLUTION
v-xuxinyi-msft
Community Support
Community Support

Hi @rack201 

 

I ran the testing as follows.

 

tables:

The table acts as a slicer.

vxuxinyimsft_0-1707880639629.png

 

vxuxinyimsft_1-1707880665892.png

 

vxuxinyimsft_2-1707880677124.png

 

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.

vxuxinyimsft_4-1707880917379.png

 

vxuxinyimsft_3-1707880875567.png

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.

View solution in original post

2 REPLIES 2
v-xuxinyi-msft
Community Support
Community Support

Hi @rack201 

 

I ran the testing as follows.

 

tables:

The table acts as a slicer.

vxuxinyimsft_0-1707880639629.png

 

vxuxinyimsft_1-1707880665892.png

 

vxuxinyimsft_2-1707880677124.png

 

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.

vxuxinyimsft_4-1707880917379.png

 

vxuxinyimsft_3-1707880875567.png

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.

Amazing - thank you - that got me going!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors