Forum Discussion

krylon123's avatar
krylon123
Frequent Visitor
7 years ago

Calculate Distinct Count By Client Based on Date Slicer

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. 

 

Need Help

5 Replies

  • krylon123's avatar
    krylon123
    Frequent Visitor
    7 years ago

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

     

     

  • v-yuta-msft's avatar
    v-yuta-msft
    Community Support
    7 years ago

    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's avatar
      krylon123
      Frequent Visitor
      7 years ago

      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's avatar
        v-yuta-msft
        Community Support
        7 years ago

        Hi krylon123,

         

        Could you show a sample pbix file for further analysis?

         

        Regards,

        Jimmy Tao