cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
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

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


Thank you,
Antriksh Sharma

View solution in original post

5 REPLIES 5
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


Thank you,
Antriksh Sharma

View solution in original post

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: <>


Thank you,
Antriksh Sharma

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
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite with Arun Ulag

Featured Session: Drive Data Culture with Power BI- Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI ​

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

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.