Forum Discussion
Table filter based on distinct value post filter based on one column - Logical
- 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
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