Forum Discussion

Mentzer's avatar
Mentzer
Frequent Visitor
3 years ago
Solved

Counting Old Dates for a Specific Condition

Hello people,   How can I deal with the following situation? I have Table1 that has all the Groups that certain ID has gone through. The Table2 has the quantity of distinct Groups that the ID has g...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Mentzer ,

     

    Here I suggest you to try this code to create a calculated column.

    Count Group in Oldest Date = 
VAR _Oldest_Date =
    CALCULATE (
        MIN ( Table1[Date] ),
        FILTER ( Table1, Table1[ID] = EARLIER ( Table2[ID] ) )
    )
VAR _GROUPinOldestDate =
    CALCULATE (
        SELECTEDVALUE ( Table1[Group] ),
        FILTER (
            Table1,
            Table1[ID] = EARLIER ( Table2[ID] )
                && Table1[Date] = _Oldest_Date
        )
    )
VAR _Count_Group =
    CALCULATE (
        COUNT ( Table1[Group] ),
        FILTER (
            Table1,
            Table1[ID] = EARLIER ( Table2[ID] )
                && Table1[Group] = _GROUPinOldestDate
        )
    )
RETURN
    IF ( Table2[Change Group] > 1, _Count_Group, BLANK () )

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
3 years ago

Hi Mentzer 
Please try

Count Changed =
SUMX ( VALUES ( Table1[ID] ), IF ( [Changed Group] > 1, 1 ) )
Mentzer's avatar
Mentzer
Frequent Visitor
3 years ago

In the followin part, the Changed Group is not recognized:

IF ( [Changed Group] > 1, 1 )

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Mentzer ,

     

    Here I suggest you to try this code to create a calculated column.

    Count Group in Oldest Date = 
VAR _Oldest_Date =
    CALCULATE (
        MIN ( Table1[Date] ),
        FILTER ( Table1, Table1[ID] = EARLIER ( Table2[ID] ) )
    )
VAR _GROUPinOldestDate =
    CALCULATE (
        SELECTEDVALUE ( Table1[Group] ),
        FILTER (
            Table1,
            Table1[ID] = EARLIER ( Table2[ID] )
                && Table1[Date] = _Oldest_Date
        )
    )
VAR _Count_Group =
    CALCULATE (
        COUNT ( Table1[Group] ),
        FILTER (
            Table1,
            Table1[ID] = EARLIER ( Table2[ID] )
                && Table1[Group] = _GROUPinOldestDate
        )
    )
RETURN
    IF ( Table2[Change Group] > 1, _Count_Group, BLANK () )

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Hi Mentzer 

    Count Changed =
SUMX ( VALUES ( Table1[ID] ), IF ( DISTINCTCOUNT ( Table1[Groups] ) > 1, 1 ) )