Forum Discussion

cdnjj's avatar
cdnjj
Frequent Visitor
8 years ago
Solved

COUNTX distinct values without CALCULATE

Hello,

 

Any thoughts on how to convert the statement below into a distinct count?    Here's the trick; it needs to be accomplished without using CALCULATE (due to a circular reference limitation).

 

The 2 tables do not have a key as they are not at the same grain (daily vs line level) and trying to avoid creating an intermediate table.

 

Customer Transaction Count = COUNTX(FILTER(SalesLineData , DailySalesSummary[Date]=SalesLineData[Date], SalesLineData[TransactionNumber])

 

Thanks in advance for ideas.

 

Need Help
  • cdnjj's avatar
    cdnjj
    8 years ago

    Magic!  Corerct, I am building out a calculated column.  

     

    Thank you.  

     

     

3 Replies

  • Daniil's avatar
    Daniil
    Kudo Kingpin
    8 years ago

    cdnjj, do you want to create a calculated column?

     

    If yes, here is what you can try:

    Customer Transaction Count =
COUNTROWS (
    GROUPBY (
        FILTER (
            SalesLineData,
            DailySalesSummary[Date] = SalesLineData[Date]
        ),
        SalesLineData[TransactionNumber]
    )
)
    • cdnjj's avatar
      cdnjj
      Frequent Visitor
      8 years ago

      Magic!  Corerct, I am building out a calculated column.  

       

      Thank you.  

       

       

  • TomMartens's avatar
    TomMartens
    Super User
    8 years ago

    Hey,

     

    It would be really helpful if you would provide sample data.

     

    But my first idea would be

     

    COUNTROWS(
  VALUES(SalesLineData[TransactionNumber])
)

    Hope this helps

     

    Regards

    Tom