Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

One row with two months into one summed month column

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 MONTHERROR END MONTHDOWNTIME STARTING MONTHDOWNTIME FOLLOWING MONTH
JulyAugust495,30743,98
JanuaryFebruary422,27493,00
NovemberDecember12,04490,25
AprilMay10,80420,65
AugustSeptember496,78419,86
AugustSeptember14,17327,00
NovemberDecember422,90303,20
AugustSeptember462,17282,00
MarchApril637,19276,91
OctoberNovember40,03276,20

 

MONTHDOWNTIME
January422,27
February493,00
March637,19
April287,71
May420,65
June0,00
July495,30
August1717,10
September1028,86
October40,03
November711,14
December793,45

 

 

// ML

1 ACCEPTED SOLUTION
PattemManohar
Community Champion
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]))
 

image.png





Did I answer your question? Mark my post as a solution!

Proud to be a PBI Community Champion




View solution in original post

3 REPLIES 3
PattemManohar
Community Champion
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]))
 

image.png





Did I answer your question? Mark my post as a solution!

Proud to be a PBI Community Champion




Anonymous
Not applicable

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]))

image.png





Did I answer your question? Mark my post as a solution!

Proud to be a PBI Community Champion




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.