Forum Discussion
Filter Data Table by a Measure
I am having some trouble finding a solution to my problem that I am trying to solve for a report I am working on.
What I want to do is filter a data table by the result of a measure. A solution is alluding me, althougn I feel that I am close.
I have two tables, that have no relation, but do share some common data elements.
Table 1 ID Detected Assigned 1 PersonA PersonB 2 PersonD PersonC 3 PersonB PersonD 4 PersonE PersonA 5 PersonB PersonB
Table2 Person StatusA StatusB PersonA 1 2 PersonB 3 4 PersonC 5 6 PersonD 7 8
(In Table2, the status columns are just meant to be representative of the data).
What I am attempting to do, is filter Table2 based on the person selected in Table2. For example, if person B is selected in Table 2, then Table 1 only shows ID 1, 3 & 5 from Table1.
I have a measure which allows me to capture the selected individual in Table2, but I am having difficulty trying to come up with the filter. I tried to create a calculated column, which gives a result of 1 or 0 if the the Detected or Assigned columns match the selection of Table2 and then set a visualization level filter to only display results that have a 1. This didnt work, and in reading, it seems to be due to calcualted columns only being generated when the model is created/updated.
I feel this should be able to be done as a measure though, to give the result I want. However, I am not able to come up with a formula such that it iterates through each item in Table1 to give the result that I can filter on.
Any help or suggestions on how I can solve this is greatly appreciated.
3 Replies
You might consider using SELECTEDVALUE('table 2'[Person]) against table 2
as the argument to a FILTER() function
Matching Rows :=
VAR selected_person =SELECTEDVALUE('table2'[Person])
RETURN
FILTER('table 1',
'table 1'[Person] = selected_person
)
Perhaps try something like this for your measure:
Measure = VAR __selected = MAX('Table2'[Person]) VAR __detected = MAX('Table 1'[Detected]) VAR __assigned = MAX('Table 1'[Assigned]) RETURN IF(__selected = __detected || __selected = __assigned,1,0)Then add this measure to your Filters and filter for 1
Hi Greg, thanks for the suggestion. I was able to get the Detected and Assigned with what you provided, but the first part for selected wasnt working. I tried to display that on the data table, but when a record is selected, that column is blank. I tried with my original measure that gets that value and I am seeing the wierdest behavior. When I select a record from Table2, if I display my measure on a card, it displays the correct value for Person, but if i try to display that value on the data table, it shows as if no record is selected, so my comparison doesn't give the proper results,
