Filter one column with reference to two other columns

[patlopes]

Hi,

I have a table as sample below 

I need to filter the table by Category when all Sub-Categories are Inactive. In the sample, after filtering, the table would only show Cat B. 

Is it possible with DAX ?

Thank you

[tamerj1]

Hi @patlopes 

please try

FilterMeasure =
INT (
ISEMPTY (
FILTER (
CALCULATETABLE (
VALUES ( 'Table'[Status] ),
ALLSELECTED ( 'Table' ),
VALUES ( 'Table'[Category] )
),
'Table'[Status] = "Active"
)
)
)

[FreemanZ · Accepted solution]

you can also plot with the measure like below, maybe more intuitive:

[FreemanZ]

hi @patlopes 

try to plot a visual with Category and filter the visual with a measure like:

Measure = 
VAR _list= 
CONCATENATEX(
    VALUES(TableName[Status]), 
    TableName[Status]
)
RETURN
IF(
    _list = "Inactive",
    1, 0
)

it worked like: