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
MightyMicrobe
Helper II
Helper II

Dealing with concatenated data in DAX

Hi everyone, 

I have a weird dataset, and very limited control over it, i.e. I can't transform data in Power Query (don't ask). 

 

The data basically looks like the table below and I need to be able to count the SUM of column 2 by any ID in column 1 or a combination of them.

 

The logic is not set in stone, so users need a degree of control over how to slice the data. For example, some may need to know the SUM of all instances of 1-UEVLP regardless of other IDs, but others will only need that ID only; rinse and repeat for each ID in the list. 

 

The IDs can come in any order and there can be one or more in each record. I have a list of all possible IDs at this point but would like to make it dynamic in case new ones will pop up in the future. 

 

I thought I was pretty good with DAX by now, but I don't even know where to start. I will need to build some sort of temporary tables summarising the values, I guess? 

 

Any ideas?

 

1-UEVLP,a090001,a092002,a095001,a095003325716
1-UEVLP,a090001,a092002,a095001,a101001306857
a016001,a090002,a095002,a095003,a1010011171551
1-UEVLP,a016001,a090001,a090002,a092002,a0950011507506
1-UEVLP,a016001,a090001,a090002,a095001,a0950021607063
1-UEVLP,a016001,a090001,a090002,a095001,a0950031575770
1-UEVLP,a016001,a090001,a090002,a095001,a1000021547156
1-UEVLP,a016001,a090001,a090002,a095001,a1010011534487
1-UEVLP,a016001,a090001,a090002,a092002,a095001,a0950021650842
1 ACCEPTED SOLUTION
DataInsights
Super User
Super User

@MightyMicrobe,

 

This solution uses the pattern provided by Chris Webb. I wrapped the base pattern in GENERATE, in order to iterate each row of the table.

 

https://blog.crossjoin.co.uk/2018/05/16/a-new-approach-to-handling-ssrs-multi-valued-parameters-in-d... 

 

1. Create a calculated column to replace comma with pipe (vertical bar). The pipe symbol is the delimiter of the PATHLENGTH function.

 

ID Pipe = 
SUBSTITUTE ( Table1[ID Raw], ",", "|" )

 

DataInsights_0-1628696006106.png

 

2. Create calculated table:

 

Table2 = 
GENERATE (
    SUMMARIZE ( Table1, Table1[ID Pipe], Table1[Amount] ),
    VAR vID = Table1[ID Pipe]
    VAR vIDCount =
        PATHLENGTH ( vID )
    VAR vNumberTable =
        GENERATESERIES ( 1, vIDCount, 1 )
    VAR vIDTable =
        GENERATE (
            vNumberTable,
            VAR CurrentKey = [Value] RETURN ROW ( "@ID", PATHITEM ( vID, CurrentKey ) )
        )
    VAR vResult =
        SELECTCOLUMNS ( vIDTable, "ID", [@ID] )
    RETURN
        vResult
)

 

DataInsights_2-1628696179770.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

1 REPLY 1
DataInsights
Super User
Super User

@MightyMicrobe,

 

This solution uses the pattern provided by Chris Webb. I wrapped the base pattern in GENERATE, in order to iterate each row of the table.

 

https://blog.crossjoin.co.uk/2018/05/16/a-new-approach-to-handling-ssrs-multi-valued-parameters-in-d... 

 

1. Create a calculated column to replace comma with pipe (vertical bar). The pipe symbol is the delimiter of the PATHLENGTH function.

 

ID Pipe = 
SUBSTITUTE ( Table1[ID Raw], ",", "|" )

 

DataInsights_0-1628696006106.png

 

2. Create calculated table:

 

Table2 = 
GENERATE (
    SUMMARIZE ( Table1, Table1[ID Pipe], Table1[Amount] ),
    VAR vID = Table1[ID Pipe]
    VAR vIDCount =
        PATHLENGTH ( vID )
    VAR vNumberTable =
        GENERATESERIES ( 1, vIDCount, 1 )
    VAR vIDTable =
        GENERATE (
            vNumberTable,
            VAR CurrentKey = [Value] RETURN ROW ( "@ID", PATHITEM ( vID, CurrentKey ) )
        )
    VAR vResult =
        SELECTCOLUMNS ( vIDTable, "ID", [@ID] )
    RETURN
        vResult
)

 

DataInsights_2-1628696179770.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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.