Create new table from two table and group by year

[TaufikMaggangka]

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

[Icey · Accepted solution]

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.

[Icey · Accepted solution]

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.

[TaufikMaggangka]

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.

[Icey · Accepted solution]

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.

[TaufikMaggangka]

Thanks @IceyThank you so much. this is work.

[daXtreme]

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