COUNTX distinct values without CALCULATE

[cdnjj]

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.

[Daniil]

@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 · Accepted solution]

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

Thank you.  

[TomMartens]

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