Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin 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.
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
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
10 | |
9 | |
8 | |
8 | |
8 |
User | Count |
---|---|
13 | |
12 | |
11 | |
10 | |
8 |