Forum Discussion
laverma
3 years agoRegular Visitor
Table filter based on distinct value post filter based on one column - Logical
I have a source table below 1. I need to filter the table with the column as 'Assigned'(My assigned have userprinicpalname from office 365 and azure) = UserPrincipalName, with the result...
- 3 years ago
See if this works.
A) If the user is hard-coded: Create the following measure to use as a filter in the filter pane, setting the value to 1:
User 1 Plan Name = VAR _U1 = CALCULATETABLE ( VALUES ( fTable[Plan Name] ), ALL ( fTable ), fTable[Assigned] = "User 1" ) VAR _All = CALCULATETABLE ( VALUES ( fTable[Plan Name] ), ALL ( fTable[Assigned] ) ) RETURN COUNTROWS ( INTERSECT ( _All, _U1 ) )B) If you need the User selection to be dynamic:
Create a new, independent table for Users (I've called mine 'Select Users"). Leave this new table unrelated in the model:
Select Users = DISTINCT(fTable[Assigned])Use this measure as a filter in the filter pane and set the value to 1:
Sel User Plan Name = VAR _U1 = CALCULATETABLE ( VALUES ( fTable[Plan Name] ), FILTER ( ALL ( 'fTable' ), fTable[Assigned] IN VALUES ( 'Select Users'[Sel Assigned] ) ) ) VAR _All = VALUES ( fTable[Plan Name] ) RETURN COUNTROWS ( INTERSECT ( _All, _U1 ) )Attached is the sample PBIX file
laverma
3 years agoRegular Visitor
PaulDBrown Could you please help ?