Forum Discussion
Filter table based off another filtered table
- 7 years ago
try with this measure and then using a filter > 0
Measure =
COUNTROWS (
CALCULATETABLE (
VALUES ( TableA[StoreName] ),
TREATAS ( VALUES ( TableB[State] ), TableA[State] ),
TREATAS ( VALUES ( TableB[Category] ), TableA[Category] ),
EXCEPT ( VALUES ( TableA[StoreName] ), VALUES ( TableB[StoreName] ) )
)
) - 7 years ago
HI, Liaise
You can try this formula as below:
Measure2 = VAR _TABLE2=CALCULATETABLE(VALUES(TableB[StoreName]),ALLEXCEPT(TableB,TableB[Category])) RETURN VAR A=COUNTROWS ( CALCULATETABLE ( FILTER(VALUES ( TableA[StoreName] ),NOT TableA[StoreName] in _TABLE2), TREATAS ( VALUES ( TableB[State] ), TableA[State] ), TREATAS ( VALUES ( TableB[Category] ), TableA[Category] ) )) RETURN VAR B=COUNTROWS(TableA) RETURN IF(ISFILTERED(TableB[Category])&&ISFILTERED(TableB[State]),A,B)Result:
IF not select from slicer
then I filter them
here is pbix, please try it.
Best Regards,
Lin
HI, Liaise
You can try this formula as below:
Measure2 =
VAR _TABLE2=CALCULATETABLE(VALUES(TableB[StoreName]),ALLEXCEPT(TableB,TableB[Category])) RETURN
VAR A=COUNTROWS (
CALCULATETABLE (
FILTER(VALUES ( TableA[StoreName] ),NOT TableA[StoreName] in _TABLE2),
TREATAS ( VALUES ( TableB[State] ), TableA[State] ),
TREATAS ( VALUES ( TableB[Category] ), TableA[Category] )
)) RETURN
VAR B=COUNTROWS(TableA) RETURN
IF(ISFILTERED(TableB[Category])&&ISFILTERED(TableB[State]),A,B)Result:
IF not select from slicer
then I filter them
here is pbix, please try it.
Best Regards,
Lin
Thank you for your help also. Your solution did work but required both filters to have a selection which is not always required in my situation. But still a great solution and appreciate it