Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Responsive Resident
Responsive Resident

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
Community Champion
Community Champion

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
Community Champion
Community Champion

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

kriscoupe
Responsive Resident
Responsive Resident

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

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors