Request help with Concatenating the results in a single row

[rajdivate]

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.

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

[rajdivate]

Thank You Very Much!

[v-cgao-msft · Accepted solution]

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.

Create relationships.

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