Forum Discussion
DAX Function to Countif
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"))
4 Replies
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!
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().
