locked
CountRows, where rows contain certain values RRS feed

  • Question

  • Im trying to determine if a returned table contains certain values, and then count the rows in that table if it does.

    In the below data set, i want to return the orderid for orders that contain itemID 2
    I can do a count of everything, but having trouble with the filtering, i.e. where order contains itemid 2. Any Help would be appreciated. Thanks!

    Countord:=CALCULATE(COUNTROWS(orders), ALLEXCEPT(orders,orders[orderID]))

    OrderID   ItemID
    1             1
    1             2
    1             3
    1             4
    2             1
    2             3
    3             2
    3             3



    Wednesday, August 20, 2014 4:24 PM

Answers

  • Ok, now with that. Try this.

    COUNTROWS
    (
    	CALCULATETABLE
    	(
    		'Orders',
    		CALCULATETABLE
    		(
    			VALUES('Orders'[OrderID]),
    			CALCULATETABLE
    			(
    				'Orders',
    				'Orders'[ItemID] = 1
    			)
    		),
    		CALCULATETABLE
    		(
    			VALUES('Orders'[OrderID]),
    			CALCULATETABLE
    			(
    				'Orders',
    				'Orders'[ItemID] = 2 || 'Orders'[ItemID] = 3
    			)
    		)
    	)
    )
    Reeves


    Denver, CO

    • Marked as answer by winstonSmith Thursday, August 21, 2014 8:01 AM
    Wednesday, August 20, 2014 9:41 PM

All replies

  • Try like this

    COUNTROWS(FILTER(orders,orders[orderID]=="2"))


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Wednesday, August 20, 2014 4:38 PM
  • I would agree with Visakh, but I'm more of a fan of CALCULATE and might write it like this: 

    COUNTROWS(CALCULATETABLE(orders, orders[orderID] = 2))

    CALCULATE give better performance over FILTER.

    Hope this helps.

    Reeves


    Denver, CO

    Wednesday, August 20, 2014 5:30 PM
  • I would agree with Visakh, but I'm more of a fan of CALCULATE and might write it like this: 

    COUNTROWS(CALCULATETABLE(orders, orders[orderID] = 2))

    CALCULATE give better performance over FILTER.

    Hope this helps.

    Reeves


    Denver, CO

    Not always :-)

    Make sure you read this

    http://www.sqlbi.com/articles/filter-vs-calculatetable-optimization-using-cardinality-estimation/

    http://sqlblog.com/blogs/marco_russo/archive/2014/03/06/lastdate-vs-max-calculatetable-vs-filter-it-depends-dax-powerpivot-tabular.aspx


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Wednesday, August 20, 2014 5:52 PM
  • Visakh, 

    Thanks for the heads up on this. Might just have to change my ways. 

    Thanks,

    Reeves


    Denver, CO

    Wednesday, August 20, 2014 5:57 PM
  • Thanks for the replies all, but that is not the answer. I think i need to be clearer articulating my problem. apologies. Let me rephrase and elaborate and expand on the fake dataset.

    orderid          itemid
    1                   1
    1                   2
    1                   3
    2                   1
    3                   2
    3                   3
    4                   1
    4                   2

    Im trying to identify any orders that contain a row with ItemID 2, along with an a row that contains any of the following itemID's "2 or 3".

    from the above dataset, that means I would identify orders 1 & 4 as fulfilling all cryteria. Im not sure how to implement this though.


    Wednesday, August 20, 2014 6:59 PM
  • Just to make sure I see this correctly. 

    I would think that the above dataset 1, 3, and 4 would meet your criteria, because order 3 has item 2 and 3.

    Is that correct?

    Thanks,

    Reeves


    Denver, CO

    Wednesday, August 20, 2014 7:07 PM
  • no, only orderID 1 & orderID 4 would meet the cryteria as only those orders contain itemID 1, and any one or more of the following itemID's (2,3)
    Wednesday, August 20, 2014 7:10 PM
  • i have gotten this far. the calculation returns counts for any orders that contain itemID 1. Im still unsure how to now check which of these orders also contain rows with ItemID 2 and/or itemID 3

    qualifyingRows:=contains(CALCULATETABLE(orders,ALLEXCEPT(orders,orders[orderid])),[itemid],1)

    Wednesday, August 20, 2014 7:39 PM
  • Winston, 

    Still not sure I follow what you are after but if you want to count all items in an order that have an item 1this should work.

    COUNTROWS
    (
    	CALCULATETABLE
    	(
    		'Orders',
    		CALCULATETABLE
    		(
    			VALUES('Orders'[OrderID]),
    			CALCULATETABLE
    			(
    				'Orders',
    				'Orders'[ItemID] = 1
    			)
    		)
    	)
    )

    Hope this helps.

    Reeves



    Denver, CO

    Wednesday, August 20, 2014 8:37 PM
  • Thanks Reeves. I have unfortunately  (or fortunately)gotten that far already!

    An order can have many lines.
    if an order has at least one line with ItemId of 1

    AND

    any of the other lines in the order are for ItemID 2 or ItemID 3 then I want to return True for that order.


    Wednesday, August 20, 2014 9:24 PM
  • Ok, now with that. Try this.

    COUNTROWS
    (
    	CALCULATETABLE
    	(
    		'Orders',
    		CALCULATETABLE
    		(
    			VALUES('Orders'[OrderID]),
    			CALCULATETABLE
    			(
    				'Orders',
    				'Orders'[ItemID] = 1
    			)
    		),
    		CALCULATETABLE
    		(
    			VALUES('Orders'[OrderID]),
    			CALCULATETABLE
    			(
    				'Orders',
    				'Orders'[ItemID] = 2 || 'Orders'[ItemID] = 3
    			)
    		)
    	)
    )
    Reeves


    Denver, CO

    • Marked as answer by winstonSmith Thursday, August 21, 2014 8:01 AM
    Wednesday, August 20, 2014 9:41 PM
  • wow! that is super close, 99% of whats needed. Fantastic! really appreciate your sharing of your expertise! 
    I hope to be able to help out in a similar fashon soon, give back just as folks like you have done here!
    Wednesday, August 20, 2014 9:49 PM