Forum Discussion

efowler's avatar
efowler
Helper II
3 years ago
Solved

Distinct Count when looking at multiple fields in table

Hello...  I have a long standing challange to properly calucate what I call "Distinct Repair".   I have a sample data table attached which shows the overall data and column E shows what would be considered a Count = 1.  My notes are below and this data is related to repair data on a product. 

 

Goal = To count distinct repairs in which there are more than one rows (unique Claim #) for the same product on the same date with same repair part. 

 

Challange = A single repair can show in the data set as two rows each with a unique claim #.  When the Product Serial #, PFP, and Repair Date are all the same between the two row it should be counted a Distinct Repair = 1

 

Any ideas on how to write the DAX for this? 

LINK TO SAMPLE DATA SET 

Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    3 years ago

    Hi, efowler 

    Please check if formula below could help:

    Result =
VAR min_Claim =
    MINX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Product SN] = EARLIER ( 'Table'[Product SN] )
                && 'Table'[PFP] = EARLIER ( 'Table'[PFP] )
                && 'Table'[Repair Date] = EARLIER ( 'Table'[Repair Date] )
        ),
        'Table'[Claim #]
    )
RETURN
    IF ( 'Table'[Claim #] = min_Claim, 1, BLANK () )

     

    Best Regards,
    Community Support Team _ Eason
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • v-easonf-msft's avatar
    v-easonf-msft
    Community Support
    3 years ago

    Hi, efowler 

    Please check if formula below could help:

    Result =
VAR min_Claim =
    MINX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Product SN] = EARLIER ( 'Table'[Product SN] )
                && 'Table'[PFP] = EARLIER ( 'Table'[PFP] )
                && 'Table'[Repair Date] = EARLIER ( 'Table'[Repair Date] )
        ),
        'Table'[Claim #]
    )
RETURN
    IF ( 'Table'[Claim #] = min_Claim, 1, BLANK () )

     

    Best Regards,
    Community Support Team _ Eason
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.