Filter table based on multiple column values

Anonymous — Thu, 26 Nov 2020 13:47:01 GMT

Hi, I have list of projects and variuos business units (Cargo, Link, Infra). I need the slicer to be able to filter the projects table based on those column values. Here's what I have now:

For the slicer I created an extra table only with the list of business units and thought will go with a measure like this:

Selected Metric =
SWITCH(
TRUE(),
SELECTEDVALUE('BusinessUnit'[BU]) = "LINK", CALCULATE('Portfolio status', 'Portfolio status'[LINK]="Yes"),
SELECTEDVALUE('BusinessUnit'[BU]) = "CARGO", CALCULATE('Portfolio status', 'Portfolio status'[CARGO]="Yes"),
SELECTEDVALUE('BusinessUnit'[BU]) = "INFRA",CALCULATE('Portfolio status', 'Portfolio status'[INFRA]="Yes"))

But it does not work this way as getting "the expression refers to multiple columns. multiple columns cannot be converted to a scalar value". Could you advise on how I can get the desired result ?

Re: Filter table based on multiple column values

amitchandak — Thu, 26 Nov 2020 14:34:44 GMT

Anonymous , no measure selected in calculate. Used countrows for example

Selected Metric =
SWITCH(
TRUE(),
SELECTEDVALUE('BusinessUnit'[BU]) = "LINK", CALCULATE(countrows('Portfolio status'), 'Portfolio status'[LINK]="Yes"),
SELECTEDVALUE('BusinessUnit'[BU]) = "CARGO", CALCULATE(countrows('Portfolio status'), 'Portfolio status'[CARGO]="Yes"),
SELECTEDVALUE('BusinessUnit'[BU]) = "INFRA",CALCULATE(countrows('Portfolio status'), 'Portfolio status'[INFRA]="Yes")

)

topic Filter table based on multiple column values in DAX Commands and Tips

Filter table based on multiple column values

Re: Filter table based on multiple column values