DAX: Count Distinct Values by Ref & location

Question

I am trying to count the distinct "locations" per "Shipment Ref".

I have the following formula which gives me the total count of locations by shipment ref but not the distinct count.

ShipLocationCount =
COUNTX (
    FILTER (
        CustomerProfitabilityReport_Full_D365,
        EARLIER ( CustomerProfitabilityReport_Full_D365[SHIPMENT_REF] )
            = CustomerProfitabilityReport_Full_D365[SHIPMENT_REF]
    ),
    CustomerProfitabilityReport_Full_D365[DELIVERY_ADDRESS_CODE]
)

I want to then run a measure and use this calculated column as a filter to say where ShipLocationCount > 1.

Thanks

Alexis Olson Alexis Olson · Accepted Answer · 2019-03-19T17:29:10

How about something like this instead of a filter?

ShipLocationCount =
CALCULATE (
    DISTINCTCOUNT ( CustomerProfitabilityReport_Full_D365[DELIVERY_ADDRESS_CODE] ),
    FILTER (
        CustomerProfitabilityReport_Full_D365,
        CustomerProfitabilityReport_Full_D365[SHIPMENT_REF]
            = MAX ( CustomerProfitabilityReport_Full_D365[SHIPMENT_REF] )
    )
)

You could also try something like this:

ShipLocationCount =
COUNTROWS (
    SUMMARIZE (
        CustomerProfitabilityReport_Full_D365,
        CustomerProfitabilityReport_Full_D365[SHIPMENT_REF],
        CustomerProfitabilityReport_Full_D365[DELIVERY_ADDRESS_CODE]
    )
)

The SUMMARIZE function groups distinct combinations of the columns referenced.

Edit: If you intend to use these formulas as a calculated column rather than as a measure, then they need to be modified to take into account the row context they live in.

ShipLocationCount =
    CALCULATE (
        DISTINCTCOUNT ( CustomerProfitabilityReport_Full_D365[DELIVERY_ADDRESS_CODE] ),
        ALLEXCEPT(
            CustomerProfitabilityReport_Full_D365,
            CustomerProfitabilityReport_Full_D365[SHIPMENT_REF]
        )
    )

And

ShipLocationCount =
COUNTROWS (
    SUMMARIZE (
        FILTER (
            CustomerProfitabilityReport_Full_D365,
            CustomerProfitabilityReport_Full_D365[SHIPMENT_REF]
              = EARLIER( CustomerProfitabilityReport_Full_D365[SHIPMENT_REF] )
        ),
        CustomerProfitabilityReport_Full_D365[DELIVERY_ADDRESS_CODE]
    )
)

DAX: Count Distinct Values by Ref & location

1 Answers