Forum Discussion

Boopep's avatar
Boopep
Icon for Helper I rankHelper I
1 year ago
Solved

Counting Specific ClaimSeriesNumber if there is multiple entry

Asking for a DAX command to count only the ClaimSeriesNumber that is if there is a duplicate  ClaimSeriesNumber count only the one with the latest DateReceived. 

 

  • MasonMA's avatar
    MasonMA
    1 year ago

    Hello Boopep 

     

    If you only want a count of distinct ClaimSeriesNum considering their latest DateReceived (no need to see other columns) and if duplicates with the same latest date don’t matter, 

    COUNTROWS (
    SUMMARIZE (
        table,
        table[ClaimSeriesNum],
        "LatestDate", MAX ( table[DateReceived] )
    )
)

     

6 Replies

  • MasonMA's avatar
    MasonMA
    Icon for Super User rankSuper User
    1 year ago

    Hello Boopep 

     

    If you only want a count of distinct ClaimSeriesNum considering their latest DateReceived (no need to see other columns) and if duplicates with the same latest date don’t matter, 

    COUNTROWS (
    SUMMARIZE (
        table,
        table[ClaimSeriesNum],
        "LatestDate", MAX ( table[DateReceived] )
    )
)

     

    • Boopep's avatar
      Boopep
      Icon for Helper I rankHelper I
      1 year ago

      Thank you.... i tested it it correct.... how about if i want to display only the total CLAIMSAMOUNT of those "LatestDate" in a Card. What would be the DAX measure

      • MasonMA's avatar
        MasonMA
        Icon for Super User rankSuper User
        1 year ago

        This isnt as intuitive as your original request. I'd suggest opening another post with your data pasted in your message in a right format (not images) so that other users can test in their pbi desktop and give your a correct solution:) 

  • rohit1991's avatar
    rohit1991
    Icon for Super User rankSuper User
    1 year ago

    Hi Boopep 

    Could you please try this measure:

    Count Latest ClaimSeries =
VAR LatestRows =
    FILTER (
        ALLSELECTED ( 'Claims' ),
        NOT ISBLANK ( 'Claims'[DateReceived] ) &&
        'Claims'[DateReceived] =
            CALCULATE (
                MAX ( 'Claims'[DateReceived] ),
                ALLEXCEPT ( 'Claims', 'Claims'[ClaimSeriesNumber] )
            )
    )
RETURN
COUNTROWS ( SUMMARIZE ( LatestRows, 'Claims'[ClaimSeriesNumber] ) )