Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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!
Solved! Go to Solution.
@Anonymous
For example we don't want any row with product A/ignore all the orders if it has A
Now you can filter 1 to get the desired output
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
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] )
)
@Anonymous
For example we don't want any row with product A/ignore all the orders if it has A
Now you can filter 1 to get the desired output
User | Count |
---|---|
21 | |
19 | |
12 | |
9 | |
7 |
User | Count |
---|---|
30 | |
27 | |
14 | |
13 | |
11 |