topic Re: Filter multiple Rows with same value (in column 1) when a value (in column 2) is given in DAX Commands and Tips

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

Anonymous — Thu, 18 Aug 2022 15:26:22 GMT

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:

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!

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

ribisht17 — Thu, 18 Aug 2022 16:28:46 GMT

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)

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

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

johnt75 — Thu, 18 Aug 2022 16:19:49 GMT

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] ) )

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

Anonymous — Fri, 19 Aug 2022 04:18:59 GMT

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

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

jimpatel — Thu, 08 Feb 2024 09:29:22 GMT

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

Any idea please?

Thanks a lot