Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 |
---|---|
22 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
25 | |
13 | |
11 | |
9 | |
6 |