topic Re: Create new table from two table and group by year in DAX Commands and Tips

Create new table from two table and group by year

TaufikMaggangka — Thu, 07 Apr 2022 06:56:14 GMT

Hi Expert,

Need help to create new table(Not by data trasformation but by virtual table), to combine two table into one table.
Here the illustrationL

Class Table

Year	SchoolId	ClassName
2021	1	A
2021	1	B
2021	1	C
2021	1	D
2021	1	E
2021	1	F
2021	2	G
2021	2	H
2021	2	I
2021	2	J
2021	2	K
2021	2	L
2022	1	AA
2022	1	AB
2022	1	AC
2022	2	BA
2022	2	BB
2022	2	BC

Date Table
Table that contain date from 2021 to 2022.

Basically I want to combine the two table and filtering by year, so the class in 2021 will only show in 2021, same with class in 2022 when we generating the new table.

Thank so much for help, stuck few days with this. quite new with power bi. If need more explanation let me know

Re: Create new table from two table and group by year

daXtreme — Thu, 07 Apr 2022 09:03:39 GMT

How to Get Your Question Answered Quickly (powerbi.com)

Re: Create new table from two table and group by year

Icey — Mon, 11 Apr 2022 08:16:07 GMT

Hi TaufikMaggangka ,

Do you mean something like so?

Table = FILTER ( CROSSJOIN ( Class, 'Date' ), Class[Year] = YEAR ( 'Date'[Date] ) )

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Create new table from two table and group by year

TaufikMaggangka — Wed, 25 May 2022 09:25:45 GMT

Hi Icey ,
related to this approach, it is working fine. But I have an issue.
Is there a way to do it, If want to only retrive only specific column in both table. I try this way but it is merged all field on the both table. in my case My table more complicated, If I follow your way, it is increase the size of the power bi file, and the performance being slowly.

for example, Table Class.

School ID	ClassID	ClassName	Year
1	1	A	2021
1	2	B	2021
1	3	C	2022
2	11	AA	2021
2	22	BB	2022
2	33	CC	2022
3	111	AAA	2021
3	222	BBB	2021

In this case, I only want to retrive SchoolId, and Classid and not including classname.

Really need your help,
I try to modified your dax, but cannot solve it.

Re: Create new table from two table and group by year

Icey — Wed, 25 May 2022 09:41:32 GMT

Hi TaufikMaggangka ,

Try this:

Table = FILTER ( CROSSJOIN ( SUMMARIZE ( Class, Class[SchoolId], Class[Classid], Class[Year] ), 'Date' ), Class[Year] = YEAR ( 'Date'[Date] ) )

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Create new table from two table and group by year

TaufikMaggangka — Wed, 25 May 2022 10:04:58 GMT

Thanks IceyThank you so much. this is work.