Forum Discussion

dfort's avatar
dfort
Frequent Visitor
4 years ago
Solved

Power BI count with multiple criterial

Hi All, 

 

I have data that consists of 476 unique part numbers that are purchased items. 

 

each of the 476 items have either 0 in stock or stock on hand. 

 

I want to get a count of these 476 purchased items that have stock (Inv_Calgary) greater than 0.

 

 

 

Example from the table above I would want a Card to show 3 meaning 3 of the 4 items are in stock. then we can focus on that 1 part that is not.

 

I can do this in excel with countifs etc, but not sure the power bi dax side.

 

I used this to get the 476 items 

 

CountPurchaseItems =
CALCULATE(
DISTINCTCOUNT(Dashboard[No.]),
Dashboard[Make/Buy]="Purchase")

 

 

 

 

 

Need Help
  • SteveHailey's avatar
    SteveHailey
    4 years ago

    Ah, I thought Inventory Calgary was a column. Since it's a measure, try:

    Count of Items with Stock > 0 = 
CALCULATE(
    DISTINCTCOUNT(Dashboard[No.] ),
    FILTER( 
        Dashboard,
        Dashboard[Make/Buy] = "Purchase" &&  [Inv_Calgary] > 0
    )
)

7 Replies

  • SteveHailey's avatar
    SteveHailey
    Icon for Solution Specialist rankSolution Specialist
    4 years ago

    Hello dfort, 

    I think this should be what you're looking for:

    Count of Items with Stock > 0 = 
COUNTROWS(
    FILTER( Dashboard,
    Dashboard[Make/Buy] = "Purchase" && Dashboard[Inv_Calgary] > 0 
    )
)
  • dfort's avatar
    dfort
    Frequent Visitor
    4 years ago

    SteveHailey 

     

    YES, that did work!  thank you


    one thing I forgot to mention in my data set is it may have the same item number showing up a few times, so how would I add it to count unique item numbers that are purchased with more stock than 0? 

    • SteveHailey's avatar
      SteveHailey
      Icon for Solution Specialist rankSolution Specialist
      4 years ago

      You're welcome!

       

      In that case, this should do the trick:

      Count of Items with Stock > 0 = 
CALCULATE(
    DISTINCTCOUNT(Dashboard[No.] ), 
    Dashboard[Make/Buy] = "Purchase",
    Dashboard[Inv_Calgary] > 0
)
      • dfort's avatar
        dfort
        Frequent Visitor
        4 years ago

        I am getting this error 

        I believe it could be that my Inventory Calgary is a measure adding up 2 bin locations in this warehouse?