Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi I have two tables:
Table1
Month Year | | Sales |
Jan-20 | 1000 |
Feb-20 | 2000 |
Mar-20 | 500 |
Table2
Month Year | | Projections |
Jan-20 | 500 |
Feb-20 | 2000 |
Mar-20 | 1000 |
Apr-20 | 5000 |
Let's say MARCH is current month, I want a measure to provide result so that all previous months are taken from Table1 and current month and onwards data comes from Table2, example below:
RESULT
Month Year | | Projections |
Jan-20 | 1000 |
Feb-20 | 2000 |
Mar-20 | 1000 |
Apr-20 | 5000 |
Thanks for your help.
Solved! Go to Solution.
Hi @mb0307 ,
Sorry for my late reply.
You could use SUMMARIZE() to select columns when there are different number of columns between tables :
Table 3 =
VAR t1 =
FILTER (
SUMMARIZE ( 'Previous', [ Sales], Previous[Month Year ] ),
MONTH ( Previous[Month Year ] ) < 3
)
VAR t2 =
FILTER (
SUMMARIZE ( 'After', [ Projections], 'After'[Month Year ] ),
MONTH ( 'After'[Month Year ] ) >= 3
)
RETURN
UNION ( t1, t2 )
Then the new table will be like this:
Did I answer your question ? Please mark my reply as solution.
If not, please upload some insensitive data samples and expected output.
Best Regards,
Eyelyn Qin
Hi @mb0307 ,
Sorry for my late reply.
You could use SUMMARIZE() to select columns when there are different number of columns between tables :
Table 3 =
VAR t1 =
FILTER (
SUMMARIZE ( 'Previous', [ Sales], Previous[Month Year ] ),
MONTH ( Previous[Month Year ] ) < 3
)
VAR t2 =
FILTER (
SUMMARIZE ( 'After', [ Projections], 'After'[Month Year ] ),
MONTH ( 'After'[Month Year ] ) >= 3
)
RETURN
UNION ( t1, t2 )
Then the new table will be like this:
Did I answer your question ? Please mark my reply as solution.
If not, please upload some insensitive data samples and expected output.
Best Regards,
Eyelyn Qin
Hi @mb0307 ,
According to my understanding, you want to combine two tables based on current month-year , right?
You could use the following formula:
combine =
UNION (
FILTER ( 'Previous', 'Previous'[Month Year ].[MonthNo] < 3 ),
FILTER ( 'After', 'After'[Month Year ].[MonthNo] >= 3 )
)
My visualization looks like this:
Best Regards,
Eyelyn Qin
@v-eqin-msft Thanks for you response but I am getting this error: "Each table argument of 'UNION' must have the same number of columns."
My two tables have different set of columns. I want Sales and Projections union resulted in a same column please.
Thanks
Any help with this query will be much appreciated.
Thanks.
User | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |