topic Re: Summarize 2 Columns with all possible options in DAX Commands and Tips

Summarize 2 Columns with all possible options

Topjacket — Tue, 15 Oct 2024 09:17:54 GMT

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

Customer	CreatedMonthAndYear
AA	Feb 2024
BB	Mar 2024
CC	Apr 2024

Desired Output Example

Customer	CreatedMonthAndYear
AA	Feb 2024
AA	Mar 2024
AA	Apr 2024
BB	Feb 2024
BB	Mar 2024
BB	Apr 2024
CC	Feb 2024
CC	Mar 2024
CC	Apr 2024

Any Help would be great

Thanks

Re: Summarize 2 Columns with all possible options

shafiz_p — Tue, 15 Oct 2024 10:18:10 GMT

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

Re: Summarize 2 Columns with all possible options

Topjacket — Tue, 15 Oct 2024 10:29:55 GMT

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