PowerBI DAX get COUNT DISTINCT with GROUP BY , see SQL query below

Question

I have got this following SQL query that gives me the correct value from the database.

SELECT
  SUM( DISTINCT_ORDER_NUMBERS )
FROM
(
  SELECT STORE_KEY,
         COUNT( DISTINCT TRANSACTION_NUM ) AS DISTINCT_ORDER_NUMBERS,
         DATE_KEY,
         TRANSACTION_TYPE_KEY
  FROM Pos_Data
  GROUP BY STORE_KEY,
           DATE_KEY,
           TRANSACTION_TYPE_KEY
)
AS A

I am however facing challenges writing a DAX formula for a measure in Power BI Here is what I have tried so far but I get an error.

Total Number Of Orders

VAR _TotalOrders =
SUMMARIZE('Pos_Data',
    'Pos_Data'[STORE_KEY],
    'Pos_Data'[DATE_KEY],
    'Pos_Data'[TRANSACTION_TYPE_KEY],
    "DISTINCT_ORDER_NUMBERS",
    DISTINCTCOUNT('Pos_Data'[TRANSACTION_NUM]))

RETURN SUM(_TotalOrders[DISTINCT_ORDER_NUMBERS])

Please assist

Alexis Olson Alexis Olson · Accepted Answer · 2018-10-18T14:53:43

The SUM function expects a base table rather than a calculated table.

Try this instead:

VAR _TotalOrders =
SUMMARIZE('Pos_Data',
    'Pos_Data'[STORE_KEY],
    'Pos_Data'[DATE_KEY],
    'Pos_Data'[TRANSACTION_TYPE_KEY],
    "DISTINCT_ORDER_NUMBERS",
    DISTINCTCOUNT('Pos_Data'[TRANSACTION_NUM]))

RETURN SUMX(_TotalOrders, [DISTINCT_CHECK_SEQ])

Edit: If the difference you mentioned is related to nulls, then try this in place of DISTINCTCOUNT.

COUNTAX( DISTINCT( 'Pos_Data'[TRANSACTION_NUM] ), 'Pos_Data'[TRANSACTION_NUM] )

The COUNTAX function (as opposed to COUNTX) does not count nulls.

PowerBI DAX get COUNT DISTINCT with GROUP BY , see SQL query below

1 Answers