DAX Help for comma separated values

[krishnanvs]

Hi All,

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

Table 1:

Country	Value
India	10
Japan	20
S Africa	30
Brazil	40

Table 2:

Place	Country
Asia	India, Japan
Africa	S Africa
America	Brazil

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:

Place	Value
Asia	30
Africa	30
America	40

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

[Mahesh0016]

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

[BIHelper]

@krishnanvs -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

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

[Jihwan_Kim]

Hi,

Please check the below picture and the attached pbix file.

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]
        )
    )
)

[FreemanZ · Accepted solution]

hi @krishnanvs 

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:

[amitchandak]

@krishnanvs , 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