cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
loganwol Frequent Visitor
Frequent Visitor

Getting distinct values for multiple columns

Hi,

I am summarizing data from one table into another. Part of the data in the original table is a column of type text which I generate a comma delimited summary string.

 

Issues =  
    var ft = FILTER(RELATEDTABLE(Data), 
                  [ID] = Data[ID] && Data[Result] <> "Pass" && 
                  Not ISBLANK(TRIM(Data[Issue]))) 
    var issuessummary = CONCATENATEX(ft, Data[Issue], ",")
return 
     issuessummary

This works perfectly but I realized that the return values can be duplicate, so a result value string for issuessummary could equal "ABC", "DEF", "ABC", "xyz". I would like to remove the duplicate in the return string to represent "ABC", "DEF", "xyz".

Is there a way to accomplish that?


Regards

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-sihou-msft
Moderator

Re: Getting distinct values for multiple columns

@loganwol

 

In this scenario, since you have duplicate Issues for same ID, you should create a "summarized" data table for filtering the context. You can create a calcuated table like below:

 

Data2 = SUMMARIZECOLUMNS(Data[ID],Data[Issue],Data[Result])

Then you can involve the calcuated table in your formula.

 

Issues =  
    var ft = FILTER(RELATEDTABLE(Data2), 
                  [ID] = Data2[ID] && Data2[Result] <> "Pass" && 
                  Not ISBLANK(TRIM(Data2[Issue]))) 
    var issuessummary = CONCATENATEX(ft, Data2[Issue], ",")
return 
     issuessummary

Regards,

 

Simon Hou

2 REPLIES 2
Moderator v-sihou-msft
Moderator

Re: Getting distinct values for multiple columns

@loganwol

 

In this scenario, since you have duplicate Issues for same ID, you should create a "summarized" data table for filtering the context. You can create a calcuated table like below:

 

Data2 = SUMMARIZECOLUMNS(Data[ID],Data[Issue],Data[Result])

Then you can involve the calcuated table in your formula.

 

Issues =  
    var ft = FILTER(RELATEDTABLE(Data2), 
                  [ID] = Data2[ID] && Data2[Result] <> "Pass" && 
                  Not ISBLANK(TRIM(Data2[Issue]))) 
    var issuessummary = CONCATENATEX(ft, Data2[Issue], ",")
return 
     issuessummary

Regards,

 

Simon Hou

loganwol Frequent Visitor
Frequent Visitor

Re: Getting distinct values for multiple columns

Hi Simon,

When I tried the solution, I get an error "Cannot find table Data2" and I get red squiglly lines for the Data2[ID] and Data2[Result] values.

 

Regards,

Loganwol

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 6 members 3,435 guests
Please welcome our newest community members: