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
rajdivate
Frequent Visitor

Request help with Concatenating the results in a single row

Hi,

 

I have a Depts Lookup table and a master data in my data model as shown below. I have created a measure using SWITCH statement to get the status  depending on the delta between plan vs actuals.

rajdivate_0-1655488884646.png

 

The measure I used is as  below :

 

Status = Switch(

True(),

Master_Data[Delta%]> 0 && Master_Data[Detla 2 Wks%]>0, "Already Overbudget, yet adding to +ve Delta",

Master_Data[Delta%]< 0 && Master_Data[Detla 2 Wks%] <0, "Already Underbudget, yet adding to -ve Delta",

Master_Data[Delta%]> 0 && Master_Data[Detla 2 Wks%]< 0, "Improvements to +ve Delta",

Master_Data[Delta%]<0 && Master_Data[Detla 2 Wks%]>0, "Improvements to -ve Delta"

)

 

Here Delta% is the measure for calculating % difference between plan vs actuals for the entire period, Delta 2 Wks% is the measure for calculating % difference between plan vs actuals for the last 2 weeks.

 

I am able to get the status output as per below table 1 in Power BI, but I need to present the results as per table 2 provided below could you please help..

 

Output I am able to get (table 1) :

Function Mapping

Status

Design

Already Overbudget, yet adding to +ve Delta

DV

Already Overbudget, yet adding to +ve Delta

PD

Already Overbudget, yet adding to +ve Delta

SET

Already Overbudget, yet adding to +ve Delta

SPT

Improvements to +ve Delta

DFT

Improvements to -ve Delta

 

Output Required (table 2) :

              Status                                                                Function

Already Overbudget, yet adding to +ve Delta

Design,DV,PD,SET

Already Underbudget, yet adding to -ve Delta

-

Improvements to +ve Delta

SPT

Improvements to -ve Delta

DFT

 

 

Thank You,

Rajesh Divate

1 ACCEPTED SOLUTION
v-cgao-msft
Community Support
Community Support

Hi @rajdivate ,

 

Please create a table first.

Table = 
SUMMARIZE(
    'Depts_LookUP',
    'Depts_LookUP'[Function Mapping],
    "Status",
    [Status]
)

Then create a calculated column on this table.

Functions = 
CONCATENATEX(
    FILTER('Table','Table'[Status]=EARLIER('Table'[Status])),
    'Table'[Function Mapping],","
    )

Enter data.

Table2.

vcgaomsft_1-1655690571844.png

Create relationships.

vcgaomsft_2-1655690658282.png

vcgaomsft_3-1655690705116.png

Best Regards,
Gao

Community Support Team

 

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

View solution in original post

2 REPLIES 2
rajdivate
Frequent Visitor

Thank You Very Much!

v-cgao-msft
Community Support
Community Support

Hi @rajdivate ,

 

Please create a table first.

Table = 
SUMMARIZE(
    'Depts_LookUP',
    'Depts_LookUP'[Function Mapping],
    "Status",
    [Status]
)

Then create a calculated column on this table.

Functions = 
CONCATENATEX(
    FILTER('Table','Table'[Status]=EARLIER('Table'[Status])),
    'Table'[Function Mapping],","
    )

Enter data.

Table2.

vcgaomsft_1-1655690571844.png

Create relationships.

vcgaomsft_2-1655690658282.png

vcgaomsft_3-1655690705116.png

Best Regards,
Gao

Community Support Team

 

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

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.

Top Solution Authors