Forum Discussion

Topjacket's avatar
Topjacket
Helper I
1 year ago
Solved

Summarize 2 Columns with all possible options

Hi

 

I have added a new table using SUMMARIZE. All seemed to be working fine but I have just realised that some "Customers" do not have all dates which I need to display. 

 

Is it is possible to create a table from 2 columns but displaying all possible combinations?

 

Main Table = Summarize(Cases,Cases[Customer],Cases[CreatedMonthAndYear])
 
Data Example
CustomerCreatedMonthAndYear
AAFeb 2024
BBMar 2024
CCApr 2024
 
 
Desired Output Example
CustomerCreatedMonthAndYear
AAFeb 2024
AAMar 2024
AAApr 2024
BBFeb 2024
BBMar 2024
BBApr 2024
CCFeb 2024
CCMar 2024
CCApr 2024
 

Any Help would be great

 

Thanks

Summarize Columns
  • shafiz_p's avatar
    shafiz_p
    1 year ago

    Hi Topjacket  Using crossjoin, you would be able to create all kinds of possible combination. Try below code:

    DesiredTable = 
CROSSJOIN(
    DISTINCT('Cases'[Customer Created]),
    DISTINCT('Cases'[MonthAndYear])
)

    Desired output:

     

     

    Hope this helps!!

    If this solved your problem, please accept it as a solution!!

     

     

    Best Regards,
    Shahariar Hafiz

2 Replies

  • shafiz_p's avatar
    shafiz_p
    Super User
    1 year ago

    Hi Topjacket  Using crossjoin, you would be able to create all kinds of possible combination. Try below code:

    DesiredTable = 
CROSSJOIN(
    DISTINCT('Cases'[Customer Created]),
    DISTINCT('Cases'[MonthAndYear])
)

    Desired output:

     

     

    Hope this helps!!

    If this solved your problem, please accept it as a solution!!

     

     

    Best Regards,
    Shahariar Hafiz

    • Topjacket's avatar
      Topjacket
      Helper I
      1 year ago
      shafiz_p
      Well this is just delightful! Thank you very much for your help. I was reading up elsewhere and it was looking like it wasn't going to be straightforward so its great to see a very simple solution.
      Thanks again