How to filter columns and rows in a table based on certain value on another column

[romidarmawan]

Hi, I am a new user of Power BI. Could you please help me with the following issue:

I have a table with a lot of columns. I would like to use a DAX function in which it will only show some columns of the table and the value of those column only if a value on another column matches certain criteria. For Example :

I have this table

Data[No], Data[Name], Data[Value], Data[Coverage Days], Data[Description]

I would like to create a function which display a table with only 2 columns and filter the rows of those columns with Coverage Days < 5:

Data[Name], Data[Value]

How can I achieve that result? Thank You.

[v-yulgu-msft · Accepted solution]

Hi @romidarmawan,

Please new a calculated table with below DAX formula:

Result Table =
SELECTCOLUMNS (
    FILTER ( 'TData', Data[Coverage Days] < 5 ),
    "Name", Data[Name],
    "Value", Data[Value]
)

Alternatively, you can achieve this result via table visual without writing DAX.

Add Data[Name], Data[Value] to a Table visual. Add Data[Coverage Days] to visual level filter and set its value to <5.

Best regards,

Yuliana Gu

[romidarmawan]

Hi @v-yulgu-msft, Thanks for the reply.

I tried the formula you suggested, but it gave me an error prompt of "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.". 

With function SELECTCOLUMN, looks like I can't have a column with multiple values within it.

To be exact, this is what I am looking for :

I have this table 

DEPOT	PRODUCT	COV DAYS
A	Avtur	6.86
A	Premium	4
A	Solar	2.7
B	Avtur	14.3
B	Premium	14.3
B	Solar	4.3

And, I want a function that can generate this table (Coverage Days < 5):

DEPOT	PRODUCT
A	Premium
A	Solar
B	Solar

I try to avoid using report visual filtering, because it will affect others visual chart too.

[Ashish_Mathur]

Hi,

Drag Depot and Product to the Row labels.  Write this measure

=SUM(Data[Cov days])

Apply a visual filter on the meaure with a criteria of <5.  The visual filter will only affect this specific filter.