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
Anonymous
Not applicable

DAX Help for comma separated values

Hi All,

 

I have two tables as below. I do not want to create a relationship between them.

Table 1:

CountryValue
India10
Japan20
S Africa30
Brazil40

 

Table 2:

PlaceCountry
AsiaIndia, Japan
AfricaS Africa
AmericaBrazil

 

Table 2 has country mapped to places. For Asia, India and Japan has been mapped with a comma seperator. Now I need to use a DAX measure to calculate sum of Value (Table 1) based on the places. The result should be as follows:

 

PlaceValue
Asia30
Africa30
America40

 

I tried to create a measure like below but couldnt find an answer. Please help in creating a DAX measure for this.

krishnanvs_0-1672047709423.png

 

1 ACCEPTED SOLUTION
FreemanZ
Super User
Super User

hi @Anonymous 

 

As amitchandak has suggested, in occasions like this, it is always advisible to split the column to rows in Power Query.  

If your data is not big and you insist to do with DAX, you would try to

1) create a measure like this:

 

ValueSum = 
VAR _country = MAX(Table2[Country])
RETURN
CALCULATE(
    SUM(Table1[Value]),
    FILTER(
        ALL(Table1),
        CONTAINSSTRING(_country, Table1[Country])    
    )
)

 

2) plot the measure and the place column as a Table Visual.

 

I tried and it worked like this:

FreemanZ_0-1672050133084.png

 

View solution in original post

5 REPLIES 5
Mahesh0016
Super User
Super User

Mahesh0016_0-1672053359445.png

 

@Anonymous If this post helps, please consider accept as solution to help other members find it more quickly.

BIHelper
Frequent Visitor

@Anonymous -You have written the DAX Little bit correct you just need to do correction in that is this:- 

Sum =
Var getPlace=SELECTEDVALUE('Table 2'[Place])
Var getCountry=CONCATENATEX(VALUES('Table 2'[Country]),'Table 2'[Country],",")
Var getValue=CALCULATE(SUM('Table 1'[Value]),CONTAINSSTRING(getCountry,'Table 1'[Country]))
return
getValue
then you can achive your goal i tried this one.please refer the screenshot below
BIHelper_1-1672051755460.png

Mark it as a solution if it meets your requirement
Thank You!

Jihwan_Kim
Super User
Super User

Hi,

Please check the below picture and the attached pbix file.

 

Jihwan_Kim_0-1672051068500.png

 

 

Value measure: =
SUMX (
    DISTINCT ( 'Table 2'[Place] ),
    CALCULATE (
        SUMX (
            FILTER (
                GENERATE (
                    'Table 1',
                    ADDCOLUMNS (
                        ADDCOLUMNS ( 'Table 2', "@path", SUBSTITUTE ( 'Table 2'[Country], ", ", "|" ) ),
                        "@pathcontains", PATHCONTAINS ( [@path], 'Table 1'[Country] )
                    )
                ),
                [@pathcontains] = TRUE ()
                    && 'Table 2'[Place] = MAX ( 'Table 2'[Place] )
            ),
            'Table 1'[Value]
        )
    )
)

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


FreemanZ
Super User
Super User

hi @Anonymous 

 

As amitchandak has suggested, in occasions like this, it is always advisible to split the column to rows in Power Query.  

If your data is not big and you insist to do with DAX, you would try to

1) create a measure like this:

 

ValueSum = 
VAR _country = MAX(Table2[Country])
RETURN
CALCULATE(
    SUM(Table1[Value]),
    FILTER(
        ALL(Table1),
        CONTAINSSTRING(_country, Table1[Country])    
    )
)

 

2) plot the measure and the place column as a Table Visual.

 

I tried and it worked like this:

FreemanZ_0-1672050133084.png

 

amitchandak
Super User
Super User

@Anonymous , In table 2, use split by delimiters into rows and join with the first table

https://www.tutorialgateway.org/how-to-split-columns-in-power-bi/

Power Query Split Column , Split Column By Delimiter: https://youtu.be/FyO9Vmhcfag

 

 

 

refer if needed

Power BI- Text Part slicer to filter/search text - https://youtu.be/MKKWeOqFG4c

 

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.