Forum Discussion
How to filter columns and rows in a table based on certain value on another column
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
Microsoft EmployeeHi 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
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
Super User
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.
