Forum Discussion

jalaomar's avatar
jalaomar
Icon for Helper IV rankHelper IV
4 years ago
Solved

Filter a column based on another column

Hello,

 

This might be a simple solution and hopefully you can support me in this 

 

I have a table which i have sales values for projects and in the table I would like to filter out projects with a sales value below 600 only for Classification = L3

see below pic to demonstrate the input and output.

 

Need Help
  • Samarth_18's avatar
    Samarth_18
    4 years ago

    Hi jalaomar 

     

    Create a measure like below and use it as filter on your visual:-

     

    _filter = if(sum(table[sales price])< 600,1,0)

     

    Thanks,

    Samarth

     

  • v-jingzhang's avatar
    v-jingzhang
    4 years ago

    Hi jalaomar 

     

    Where do you want to see the expected output? If you want to filter out those rows from the data model, you can use Power Query editor to filter out those rows. Then only other rows will be imported into your model. 

     

    Insert a custom step below existing steps in APPLIED STEPS pane and input below code into the formula bar. Replace "Changed Type" with the previous step name in your Power Query.  

    = Table.SelectRows(#"Changed Type", each not ([Classification] = "L3" and [Sales Price] < 600))

     

    If you want to import all data rows but filter out those rows in a table visual in the report, you can create the following measure and use it as a filter on the table visual. Set the value is 1. 

    Flag = IF(SELECTEDVALUE('Input Table'[Classification]) = "L3" && SUM('Input Table'[Sales Price]) < 600,0,1)

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.

3 Replies

  • Samarth_18's avatar
    Samarth_18
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi jalaomar 

     

    Create a measure like below and use it as filter on your visual:-

     

    _filter = if(sum(table[sales price])< 600,1,0)

     

    Thanks,

    Samarth

     

  • v-jingzhang's avatar
    v-jingzhang
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi jalaomar 

     

    Where do you want to see the expected output? If you want to filter out those rows from the data model, you can use Power Query editor to filter out those rows. Then only other rows will be imported into your model. 

     

    Insert a custom step below existing steps in APPLIED STEPS pane and input below code into the formula bar. Replace "Changed Type" with the previous step name in your Power Query.  

    = Table.SelectRows(#"Changed Type", each not ([Classification] = "L3" and [Sales Price] < 600))

     

    If you want to import all data rows but filter out those rows in a table visual in the report, you can create the following measure and use it as a filter on the table visual. Set the value is 1. 

    Flag = IF(SELECTEDVALUE('Input Table'[Classification]) = "L3" && SUM('Input Table'[Sales Price]) < 600,0,1)

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.

    • jalaomar's avatar
      jalaomar
      Icon for Helper IV rankHelper IV
      4 years ago

      Hi v-jingzhang , Thanks for the input.

       

      I am struggling to count the total number of projects in a card visual based on the filter that I made in the table visual. How can I get the correct total number of projects for all visuals in the report. I could not use the measure in the card visual so that the right filter could be applied there as well.

       

      BR

      J