Calculate Distinct Count By Client Based on Date Slicer

[krylon123]

Hi,

Been working on this project for a few weeks and am stumped.

Trying to determine the distinct count of shipment modes by client name.  Am able to determine the distinct count by using the following formula, however the formula accounts for the full 3 years of the data set.  It is not dynamic when dates are changed in the date slicer.

Services = SUMMARIZE('vShipmentLineFinancials',vShipmentLineFinancials[Static Brands.Local Client Name Revised], "Count of Services", vShipmentLineFinancials[Count of Shipment], "Programs", calculate(firstnonblank(values(vShipmentLineFinancials[ProgramCode]), 1)))

Count of Shipment = calculate(DISTINCTCOUNT(vShipmentLineFinancials[Transport.Shipment Mode]), ALLSELECTED('Date'[Date].[Date]), filter(vShipmentLineFinancials, NOT(isblank(vShipmentLineFinancials[Transport.Shipment Mode]))))

I think the distinct count measure is only counting based on the 3 years of data in the vShipmentLineFinancials table.  It is completely ignoring the date slicer although there is a relationship between the date table and the vShipmentLineFinancials table.

For example, the distinct count of Client A consistently always lists 6 as the distinct count of services no matter what dates are filtered in the slicer.  Cross-checking the shipment modes with each client shows that filtering for only 1 year should show only 1 shipment mode, but the distinct count measure always shows 6.

Hoping there is an easy fix to incorporate the slicer selections into the distinct count measure. 

[v-yuta-msft]

Hi krylon123,

Modify your measure like below:

Count of Shipment =
CALCULATE (
    DISTINCTCOUNT ( vShipmentLineFinancials[Transport.Shipment Mode] ),
    ALLSELECTED ( 'Date'[Date].[Date] ),
    FILTER (
        vShipmentLineFinancials,
        vShipmentLineFinancials[Date]
            IN VALUES ( 'Date'[Date].[Date] )
                & NOT ( ISBLANK ( vShipmentLineFinancials[Transport.Shipment Mode] ) )
    )
)

Based on test, it works on my side:

 

Regards,

Jimmy Tao

[krylon123]

Hi Jimmy,

Thanks for the input.  I gave that calculation a try but have been getting an error message impacting the filter portion of the calculation.  I indicating the error portion below in red underlined text. 

Count of Shipment =
CALCULATE (
    DISTINCTCOUNT ( vShipmentLineFinancials[Transport.Shipment Mode] ),
    ALLSELECTED ( 'Date'[Date].[Date] ),
    FILTER (
        vShipmentLineFinancials,
        vShipmentLineFinancials[RecognizedDate] IN VALUES('Date'[Date].[Date])
              & NOT ( ISBLANK ( vShipmentLineFinancials[Transport.Shipment Mode] ) )
    )
)

Hoping you can help to troubleshoot the error.

[v-yuta-msft]

Hi krylon123, Have your solved your issue by now? If you have, could you please kindly mark my answer? Regards, Jimmy Tao

[v-yuta-msft]

Hi krylon123,

Could you show a sample pbix file for further analysis?

Regards,

Jimmy Tao

[krylon123]

Thought it might help if I provide a picture of the tables and their relationships.