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

 

 

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.

6 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    3 years ago

    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's avatar
      Mentzer
      Frequent Visitor
      3 years ago

      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?

  • 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.