Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Dear community,
I have the following data as shown below in the first tabel, as you can see its about error downtime, i have some errors that starts in one month and end in an other, which gives a downtime that extends over two different months, which is fine, but my problem is that the data is in the same row but for two different month. I want to combine the two month columns into one month column as shown in the second tabel, i tried using the LOOKUPVALUE function with a date tabel, but couldnt quite figure out how to do it...
Anyone that can help me?
Thank you in advance.
ERROR START MONTH | ERROR END MONTH | DOWNTIME STARTING MONTH | DOWNTIME FOLLOWING MONTH |
July | August | 495,30 | 743,98 |
January | February | 422,27 | 493,00 |
November | December | 12,04 | 490,25 |
April | May | 10,80 | 420,65 |
August | September | 496,78 | 419,86 |
August | September | 14,17 | 327,00 |
November | December | 422,90 | 303,20 |
August | September | 462,17 | 282,00 |
March | April | 637,19 | 276,91 |
October | November | 40,03 | 276,20 |
MONTH | DOWNTIME |
January | 422,27 |
February | 493,00 |
March | 637,19 |
April | 287,71 |
May | 420,65 |
June | 0,00 |
July | 495,30 |
August | 1717,10 |
September | 1028,86 |
October | 40,03 |
November | 711,14 |
December | 793,45 |
// ML
Solved! Go to Solution.
@Anonymous Please try this as a New Table
TestUnionOut = VAR _SetA = SELECTCOLUMNS(TestUnion,"MONTH",TestUnion[ERROR START MONTH],"DOWNTIME",TestUnion[DOWNTIME STARTING MONTH]) VAR _SetB = SELECTCOLUMNS(TestUnion,"MONTH",TestUnion[ERROR END MONTH],"DOWNTIME",TestUnion[DOWNTIME FOLLOWING MONTH]) VAR _All = UNION(_SetA,_SetB) RETURN GROUPBY(_All,[MONTH],"DOWNTIME",SUMX(CURRENTGROUP(),[DOWNTIME]))
Proud to be a PBI Community Champion
@Anonymous Please try this as a New Table
TestUnionOut = VAR _SetA = SELECTCOLUMNS(TestUnion,"MONTH",TestUnion[ERROR START MONTH],"DOWNTIME",TestUnion[DOWNTIME STARTING MONTH]) VAR _SetB = SELECTCOLUMNS(TestUnion,"MONTH",TestUnion[ERROR END MONTH],"DOWNTIME",TestUnion[DOWNTIME FOLLOWING MONTH]) VAR _All = UNION(_SetA,_SetB) RETURN GROUPBY(_All,[MONTH],"DOWNTIME",SUMX(CURRENTGROUP(),[DOWNTIME]))
Proud to be a PBI Community Champion
Hi @PattemManohar,
It works, but is it possible to add year too, so so i can make a slicer for years at my report level? Because right now it doesnt respond to my year slicer even though i created a relation between the months.
Acutally its kinda the same situation, i have an error starting year and an error ending year for each error.
So if it is possible to add a year column to the new tabel it would be great.
Thank you in advance.
@Anonymous In the same way, you can add year field. Here I've added 2019 as year but if you have staringyear and endingyear add those fields accordingly in _SetA and _SetB variable expressions.
TestUnionOut = VAR _SetA = SELECTCOLUMNS(TestUnion,"MONTH",TestUnion[ERROR START MONTH],"DOWNTIME",TestUnion[DOWNTIME STARTING MONTH],"YEAR",2019) VAR _SetB = SELECTCOLUMNS(TestUnion,"MONTH",TestUnion[ERROR END MONTH],"DOWNTIME",TestUnion[DOWNTIME FOLLOWING MONTH],"YEAR",2019) VAR _All = UNION(_SetA,_SetB) RETURN GROUPBY(_All,[MONTH],[YEAR],"DOWNTIME",SUMX(CURRENTGROUP(),[DOWNTIME]))
Proud to be a PBI Community Champion
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |