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

Accepted Solutions
Highlighted
Resolver IV
Resolver IV

Re: Create a table/matrix with related data of 2 queries

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

Highlighted
Super User II
Super User II

Re: Create a table/matrix with related data of 2 queries

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
Highlighted
Resolver IV
Resolver IV

Re: Create a table/matrix with related data of 2 queries

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

Highlighted
Super User II
Super User II

Re: Create a table/matrix with related data of 2 queries

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

Highlighted
Helper I
Helper I

Re: Create a table/matrix with related data of 2 queries

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.

Highlighted
Super User II
Super User II

Re: Create a table/matrix with related data of 2 queries

Just use the not equal to sign: <>


Thank you,
Antriksh Sharma
Highlighted
Helper I
Helper I

Re: Create a table/matrix with related data of 2 queries

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

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors