Counting Old Dates for a Specific Condition

[Mentzer]

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 gone through.

For example, ID=13 from Table1, we can see that it has made 4 group change (G-H-G-A), but we can say that it went through only 3 groups, (G-H-A) and that's what Table2 say to us.

Let's suppose that this table is always updated with new IDs.

So, how can I count the Groups from IDs that have a Changed Group > 1 and count only the group in the oldest Date? So, in this hypothetical case, we would count only the oldest groups from ID 10, 13 and 20.

I am very grateful for your attention.

[tamerj1]

Hi @Mentzer 
Please try

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

[Mentzer]

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

IF ( [Changed Group] > 1, 1 )

[v-rzhou-msft · Accepted solution]

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]

Hi @Mentzer 

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

[parry2k]

@Mentzer check this video on my YT channel and tweak it as you see fit How to get value of each product based on the most recent transaction - Power BI - YouTube

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

[Mentzer]

Your solution is very good, sir. Thank you very much for the video.

But when performing the first formula with TOPN, it gives a memory error and it is not possible to perform the calculations. Is there any other solution?