DAX Function to Countif

[Rbernhardt]

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"))

[Rbernhardt · Accepted solution]

Nvm. Solved it with a simple vlookup

[jsh121988]

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!

[Rbernhardt]

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.

[jsh121988]

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().