Forum Discussion

romidarmawan's avatar
romidarmawan
New Member
8 years ago
Solved

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

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's avatar
    v-yulgu-msft
    8 years ago

    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

3 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    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's avatar
      romidarmawan
      New Member
      8 years ago

      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 

      DEPOTPRODUCTCOV DAYS
      AAvtur6.86
      APremium4
      ASolar2.7
      BAvtur14.3
      BPremium14.3
      BSolar4.3

       

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

      DEPOTPRODUCT
      APremium
      ASolar
      BSolar

       

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

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        8 years ago

        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.