DISTINCTCOUNT of unrelated table

[olimilo]

I have 2 unrelated tables, Audits and Improper Offer. Trying to get the DISTINCTCOUNT of ImproperOffer[AuditID] based on the Audits[ID] but the formula below results in an error:

Column = 
    CALCULATE(
        DISTINCTCOUNT('Improper Offer'[AuditID]),
        'Audits'[ID] = 'Improper Offer'[AuditID]
    )

While the formula below results in an incorrect value (which is the total items from the unrelated table):

Column = 
    CALCULATE(
        DISTINCTCOUNT('Improper Offer'[AuditID]),
        'Audits'[ID] = MIN('Improper Offer'[AuditID])
    )

 
Is it possible to do this as a calculated column?

[danextian]

Hi @olimilo 

Try this calc column

COUNTROWS (
    // FILTER creates a table of unique AuditIDs from Improper Offer
    FILTER (
        VALUES ( 'Improper Offer'[AuditID] ),

        // Keep only the rows where the AuditID from Improper Offer
        // matches the current row context in Audits[ID]
        'Audits'[ID] = EARLIER ( 'Improper Offer'[AuditID] )
    )
    // COUNTROWS then counts how many matching rows remain
)

[Shahid12523 · Accepted solution]

For a calculated column with unrelated tables, use FILTER + EARLIER:

ImproperOfferDistinctCount =
CALCULATE(
DISTINCTCOUNT('Improper Offer'[AuditID]),
FILTER(
'Improper Offer',
'Improper Offer'[AuditID] = EARLIER('Audits'[ID])
)
)

[rohit1991]

Hi @olimilo 

I created these two sample tables. You can replace them with your own table names:

1. Audits Table

 

2. Improper Offer Table

 

Create Measure: Because the tables are unrelated, we need to use TREATAS.

ImproperOffer DistinctCount = 
VAR auditsInScope = VALUES ( Audits[ID] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Improper Offer'[AuditID] ),
        TREATAS ( auditsInScope, 'Improper Offer'[AuditID] )
    )

Outcome : 

[ryan_mayu]

@olimilo 

you can try this

ImproperOfferCount =
CALCULATE(
DISTINCTCOUNT('Improper Offer'[AuditID]),
FILTER(
'Improper Offer',
'Improper Offer'[AuditID] = 'Audits'[ID]
)
)

if this does not work,pls provide some sample data and expected output.