cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Midguel
Helper I
Helper I

Create a table/matrix with related data of 2 queries

Hi, I'm having this problem:

I have this 2  tables

Midguel_0-1593258163498.png      Midguel_1-1593258184331.png 

and has a relationship 1 to many by OP, I would like to create a column that can lists every "new op" that have the same code of the "old op", I'm thinking in something like this:

Midguel_2-1593258403148.png or something like this 

Midguel_3-1593258555647.png

it's the same idea but different format, eather of these two should do the work, but so far i can not reach a column like that, any help would be very much appreciate.

 

Thanks.

 

 

 

2 ACCEPTED SOLUTIONS
kriscoupe
Resolver IV
Resolver IV

Hi @Midguel,

 

You could add this formula as a calculated column to your old OP table.

 

All Codes Matching = 
VAR TextString =
CONCATENATEX(
    ALL(New OP), IF(OldOP[Code] = NewOP[Code], NewOP[New OP] & ","), ""
)
VAR Length = MAX( LEN(TextString) - 1, 0 )
RETURN
LEFT( TextString, Length )

 

It iterates through all the new OP table finding where the codes match that row and then concatenates the codes seperating by a comma. The rest of the DAX statement then removes the final comma that will occur as a result. You may need to adapt some of the column names to fit the tables and column names from your model.

 

Hope it helps!

 

Kris

View solution in original post

AntrikshSharma
Super User II
Super User II

You could try this as well:

 

AllCodes = 
VAR OuterOPCode = OldOP[Code]
VAR SameCodeInNewTable =
    FILTER ( ALL ( NewOP ), NewOP[Code] = OuterOPCode )
VAR Result =
    CONCATENATEX ( SameCodeInNewTable, LOWER ( [New OP] ), ", " )
RETURN
    Result

 

or

 

AllCodes2 = 
VAR OuterOPCode = OldOP[Code]
VAR SameCodesInNewOP =
    FILTER ( VALUES ( NewOP[Code] ), NewOP[Code] = OuterOPCode )
VAR Result =
    CALCULATE (
        CONCATENATEX ( VALUES ( NewOP[New OP] ), LOWER ( NewOP[New OP] ), ", " ),
        SameCodesInNewOP,
        ALL ( OldOP )
    )
RETURN
    Result

 

 allcodes.PNG

View solution in original post

5 REPLIES 5
AntrikshSharma
Super User II
Super User II

You could try this as well:

 

AllCodes = 
VAR OuterOPCode = OldOP[Code]
VAR SameCodeInNewTable =
    FILTER ( ALL ( NewOP ), NewOP[Code] = OuterOPCode )
VAR Result =
    CONCATENATEX ( SameCodeInNewTable, LOWER ( [New OP] ), ", " )
RETURN
    Result

 

or

 

AllCodes2 = 
VAR OuterOPCode = OldOP[Code]
VAR SameCodesInNewOP =
    FILTER ( VALUES ( NewOP[Code] ), NewOP[Code] = OuterOPCode )
VAR Result =
    CALCULATE (
        CONCATENATEX ( VALUES ( NewOP[New OP] ), LOWER ( NewOP[New OP] ), ", " ),
        SameCodesInNewOP,
        ALL ( OldOP )
    )
RETURN
    Result

 

 allcodes.PNG

View solution in original post

kriscoupe
Resolver IV
Resolver IV

Hi @Midguel,

 

You could add this formula as a calculated column to your old OP table.

 

All Codes Matching = 
VAR TextString =
CONCATENATEX(
    ALL(New OP), IF(OldOP[Code] = NewOP[Code], NewOP[New OP] & ","), ""
)
VAR Length = MAX( LEN(TextString) - 1, 0 )
RETURN
LEFT( TextString, Length )

 

It iterates through all the new OP table finding where the codes match that row and then concatenates the codes seperating by a comma. The rest of the DAX statement then removes the final comma that will occur as a result. You may need to adapt some of the column names to fit the tables and column names from your model.

 

Hope it helps!

 

Kris

View solution in original post

Thank you all, @kriscoupe @AntrikshSharma both worked great, thanks.

I was thinking... is there a way too exclude from this new column the OP's that are the same? something like if "old op" == "new op", "don't add to the list".

Again, thank you for the help.

Just use the not equal to sign: <>

Yes, fixed with:

All Codes Matching = 
VAR TextString =
CONCATENATEX(
    ALL(New OP), IF(OldOP[Code] = NewOP[Code] && OldOP[OldOP] <> NewOP[NewOP], NewOP[New OP] & ","), ""
)
VAR Length = MAX( LEN(TextString) - 1, 0 )
RETURN
LEFT( TextString, Length )

Thank you. 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Top Solution Authors