Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I've looked through a few suggestions for the above and tried to use them, but they didn't work. What am I doing wrong?
I got an SQL Query to pull in Orders from our ERP system.
Each order can have multiple order lines, therefore the majority of them appear multiple times with only a couple columns (supplied item, qty, price etc) being different.
A number of item numbers are flagged as 'Stock' items. I need to find out if the order has any parts that are classified as stock.
In Excel, I would use this: =COUNTIF(C2:C10,A2&"_Stock")>0
Column A = Order Number
Column B = Stock or Non-Stock
C = CONCAT(A2,"_",B2)
When I replicate this in DAX, it totals up ALL orders that end with "_Stock". I can't get it to work. See pic
(Last column on the pic is just Concat(OrderNumber,"_Stock"))
Solved! Go to Solution.
Nvm. Solved it with a simple vlookup
I'm a bit confused as to what you're trying to do. I think you want to know the order numbers that have stock items, and maybe the count.
I think you need to get away from the data view and use a Table/Matrix visual on a report page. You're basically overloading your table by trying to calculate parent values on child rows.
Create a CalcColumn:
HasStockItem = IF([ITM_Stock Profile (groups)] = "Stock", 1, 0)
// Returns 1 if the row is Stock, or 0 if not.
Then add a Table visual to a report page with Order Number and SUM of HasStockItem. On the visual filters, set SUM of HasStockItem to greater than 0 if you don't want to see orders that are not Stock. Or add a slicer to the page with HasStockItem, and select 1.
In excel terms, create a calulated column, then use a pivot table, then filter.
Hope this helps!
Hi,
Unfortunately this is only a small part of filter I will apply to the orders. I have a few more criteria to filter orders off by and I need this as a Calc column (not measure).
Some orders have 200+ parts on them and I need to include them as long as 1 part is a Stock item.
What I provided above is a calculated column, but it only operates on each row with now awareness of the table.
You could try creating a calculated table with Disctinct OrderId that creates aggreated columns. I think for that you can make use of SUMMARIZE().
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
88 | |
69 | |
61 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |