Forum Discussion

VModani's avatar
VModani
Frequent Visitor
4 years ago
Solved

How to avoid double counting when an item links to multiple items in a different column

I have data that among other things contains names and Offer/Reject action. I want to find how many names are in each action Reject or Accept. I can create 2 seperate measures for both actions and do distinctcount to count each individually. But that results in some doublecounting as some name are first reject and then offered so are counted seperately. 

 

I want to count them seperately only but need to remove if they were in the other action before.

 

Take John as an example below. He appears 4 times, 2 reject and 2 offered. I created a measure of Distinctcount and filtered by action. so I get 1 in offer and 1 in reject. I only want 1, based on the newest entry.

 

Thanks!!

 

DateNameAction
1 FebJohnReject
1 FebJohnReject
2 FebAdamReject
2 FebRobertOffer
3 FebJohnOffer 
4 FebMikeReject
4 FebMikeOffer
5 FebJohnOffer

 

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    Hi, 

    I am not quite sure which action does Mike belong to. On the same day, Mike's result is Reject and Offer.

    I guess Mike belongs to both, and the below calculation shows like this.

     

    Please check the attached pbix file.

     

     

    Distinctcount name by last action only: =
    IF (
    HASONEVALUE ( Data[Action] ),
    CALCULATE (
    COUNTROWS ( Data ),
    TREATAS (
    GROUPBY (
    ALL ( Data ),
    Data[Name],
    "@lastactiondate", MAXX ( CURRENTGROUP (), Data[Date] )
    ),
    Data[Name],
    Data[Date]
    )
    )
    )
     

     

2 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    4 years ago

    Hi, 

    I am not quite sure which action does Mike belong to. On the same day, Mike's result is Reject and Offer.

    I guess Mike belongs to both, and the below calculation shows like this.

     

    Please check the attached pbix file.

     

     

    Distinctcount name by last action only: =
    IF (
    HASONEVALUE ( Data[Action] ),
    CALCULATE (
    COUNTROWS ( Data ),
    TREATAS (
    GROUPBY (
    ALL ( Data ),
    Data[Name],
    "@lastactiondate", MAXX ( CURRENTGROUP (), Data[Date] )
    ),
    Data[Name],
    Data[Date]
    )
    )
    )
     

     

    • VModani's avatar
      VModani
      Frequent Visitor
      4 years ago

      Sorry for not making the sample properly. Mike belongs to the Offered action. I think date/time datatype can solve that. The solution itself if working. Thank You!