GroupBy and Having Clause

[Anonymous]

Hi,

How do I use the GroupBy and Having clause in my dataset? I have the following table:

[ORDER]

CUSTOMER ID   NORDER   NET AMOUNT

C1                        1               100

C1                        2              -100

C2                        3                400

C4                        4                0

C4                        5               100

C5                        6               100

I want to exclude the CUSTOMER ID C1 because he has the SUM of the NET AMOUNT=0  in another measure that counts thecustomers that have order. The SQL Statement could be this:

Select CUSTOMERS ID, SUM(NET AMOUNT)

FROM ORDER

GROUP BY CUSTOMERS ID

HAVING SUM(NET AMOUNT)<>0;

The measure that I want to filter is:

Z_Total_ORDER = CALCULATE(

DISTINCTCOUNT('ORDER'[CUSTOMER ID]))

The aim of this measure is to count the number of customers that have do at least one order.

I tried to add the filter :

FILTER('ORDER UNIT';SUM('ORDER UNIT'[Net Amount])<>0

but this consider only the rows that has 0 and so it excludes the NORDER 4 but no the customer C1 and his two orders (because the rows are 100 and -100...it doesn't sum).

Thanks

[pragathi]

Hi, I have requirement with having clause and also distinct count and sum function in two different tables.

Can anyone help me how to write the measure for it.

Below is the logic we need to implement as measure.

Count distinct userevents.outletcode having sum(userevents.sessioncount)>0/ count of distinct outlet.outletcode where b2b flag=""x"" and outlet.outletstatuscodd=""2"" and outlet.b2bstartdate<=<date selected>

Kindly help how to implement this logic in dax

[OwenAuger]

Hi @Anonymous

Here are a couple of ways you could write this measure.

The key part of the measure is FILTER( VALUES ( 'ORDER'[CUSTOMER ID] ),...), which is similar to the HAVING clause in your SQL example.

Customers with Nonzero Amount =
CALCULATE (
    DISTINCTCOUNT ( 'ORDER'[CUSTOMER ID] );
    FILTER (
        VALUES ( 'ORDER'[CUSTOMER ID] );
        CALCULATE ( SUM ( 'ORDER'[NET AMOUNT] ) ) <> 0
    )
)

Customers with Nonzero Amount =
COUNTROWS (
    FILTER (
        VALUES ( 'ORDER'[CUSTOMER ID] );
        CALCULATE ( SUM ( 'ORDER'[NET AMOUNT] ) ) <> 0
    )
)

 Regards,

Owen

[Anonymous]

Hi Owen,

thank you a lot for the answer . I've tried but it  still does not work. This is the new measure:

CALCULATE(

DISTINCTCOUNT('ORDER UNIT'[Customer Sold Id]);

FILTER (

VALUES ( 'ORDER UNIT'[Customer Sold Id] );

CALCULATE ( SUM ( 'ORDER UNIT'[JDE Row Net Amount] ) ) <> 0)

)

The name's fields are a bit differents but you can understand it. The query return the value of 226 for a instead of 225. It continues to not exclude a customer who has two order with a SUM =0.