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.
