Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Anonymous
Not applicable

Filter multiple Rows with same value (in column 1) when a value (in column 2) is given

Dear all,

I need your support in writing a DAX formula.

Situation

I have a table that contains a column "Auftragsnummer" (order no.) and a column "Artikel" (article).

There can be several article lines per order no., therefore the order no. column contains the number several times:

sibeck_77_0-1660835980528.png

Goal

If the article "ZSOPA" appears in an order, then all lines with the same order no. must be filtered out.

Problem

If I solve this with normal filtering, it filters out only the one line with "ZSOPA", but all other lines with the same order no. remain.

 

Is there a good approach or a DAX formula to reach the goal?

 

Thank you for your awsome support!

1 ACCEPTED SOLUTION
ribisht17
Community Champion
Community Champion

@Anonymous 

 

For example we don't want any row with product A/ignore all the orders if it has A

Solution = IF( ISBLANK( CALCULATE(max('Ignore'[Column2]),(ALLEXCEPT('Ignore','Ignore'[Order No.])),'Ignore'[Column2]="A")),1,0)

ribisht17_0-1660839262444.png

Now you can filter 1 to get the desired output

 

 

Thanks,
Ritesh
Mark my post as a solution if it helped you| Munde and Kudis (Ladies and Gentlemen) I like your Kudos!! !!
My YT Channel Dancing With Data !! Connect on Linkedin !!Power BI for Tableau Users 

 

View solution in original post

4 REPLIES 4
jimpatel
Post Patron
Post Patron

Thanks a lot for you post. Very helpful. What should i do for other way around please?

 

That is if there is x in column 3 then solution expected to be 1 for with respective to column2 and order number please 

jimpatel_1-1707384501733.png

 

Any idea please?

 

Thanks a lot

johnt75
Super User
Super User

You could create a table like

Orders with ZSOPA = CALCULATETABLE( VALUES( 'Table'[Auftragsnummer]), 'Table'[Artikel] = "ZSOPA")

and link that in a one-to-many relationship to the main table. On visuals you could add a filter so that the 'Orders with ZSOPA'[Auftragsnummer] is blank, and if you wanted to filter inside measures you could use

My measure = CALCULATE( some calculation,
TREATAS( { BLANK()}, 'Orders with SZOPA'[Auftragsnummer] )
)
ribisht17
Community Champion
Community Champion

@Anonymous 

 

For example we don't want any row with product A/ignore all the orders if it has A

Solution = IF( ISBLANK( CALCULATE(max('Ignore'[Column2]),(ALLEXCEPT('Ignore','Ignore'[Order No.])),'Ignore'[Column2]="A")),1,0)

ribisht17_0-1660839262444.png

Now you can filter 1 to get the desired output

 

 

Thanks,
Ritesh
Mark my post as a solution if it helped you| Munde and Kudis (Ladies and Gentlemen) I like your Kudos!! !!
My YT Channel Dancing With Data !! Connect on Linkedin !!Power BI for Tableau Users 

 

Anonymous
Not applicable

Thank you @ribisht17 , this is exactly what I was looking for!

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.