Filter a column based on another column

[jalaomar]

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.

[Samarth_18 · Accepted solution]

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 · Accepted solution]

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]

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 

[Samarth_18 · Accepted solution]

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