Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Grouping table by multiple columns

Hey there, After two days of little progress, I'm asking for help.   I got a table of customers, and the products they purchsed. I added a calculated column based on different settings, identifyin...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    5 years ago

    Anonymous - OK, this involved creating a series of columns. Your dates mess me up a little but should work fine in your region settings. See attached PBIX below sig. You want Page 21 and Table (21).

     

amitchandak's avatar
amitchandak
Super User
5 years ago

Anonymous ,

First get a new column like this

Serial Sub =
var _1 = minx(filter(table, [customer] = earlier([customer]) && [serial] >earlier([customer])),[Serial])
Var _2 = minx(filter(table, [customer] = earlier([customer]) && [serial] =_1 ),[free/susribption])
return
if([free/susribption] = "Free" , _2 <>"Free" , _1, blank())

 

with the help that get other columns

 

Start Date(Sub) =minx(filter(table, [customer] = earlier([customer]) && [serial] =earlier([Serial Sub]) ),[free/susribption])
or
Start Date(Sub) =minx(filter(table, [customer] = earlier([customer]) && [serial] =earlier([Serial Sub]) ),earlier([free/susribption]))

 

same way get an end date and you can convert ="Y", when there is Serial Sub

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hey amitchandak  Thanks for your quick reply!

    1. "Serial Sub" is supposed to bring back the subsequent "sub" serial number of each freeware (if exists)?

    2. if([free/susribption] = "Free" , _2 <>"Free" , _1, blank())  --this is a valid expression? It doesn't allow "2<>"Free" as expression inside if.

     

    Please note the serial number is only for demo purposes, it is actually a random GUI text.

    I can only match "freeware" to "sub" based on same customer and product, then identifing who is the latest freeware and first sub based on respective startdate.

    Thank you!

    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion
      5 years ago

      Anonymous - Can you post that table as text in a table so that we can easily paste it into Power BI Enter Data query?

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hey @Greg_Deckler , here is the table in plain text.

        Tried adding it in table but it marks the post as spam:

        Customer Product Serial (random GUID) StartDate EndDate Period (M) Free / Subscription
        A WA 15685 1/1/2019 31/12/2019 12 Free
        A WA 67732 1/1/2020 31/5/2020 5 Free
        A WA 12114 1/6/2020 30/6/2020 1 Sub
        A WA 60719 1/7/2020 30/6/2021 12 Sub
        A QWS 32789 1/1/2019 31/12/2019 12 Free
        A KS 48065 1/7/2020 30/6/2021 12 Sub
        B WA 18944 1/1/2020 31/5/2020 5 Free
        B WA 56311 1/6/2020 30/6/2020 1 Sub
        B KS 77724 1/7/2020 30/6/2021 12 Sub
        C QWS 19615 1/7/2020 30/6/2021 12 Sub
        C WA 70867 1/7/2020 30/6/2021 12 Sub
        D WA 30656 1/6/2020 30/6/2020 1 Sub