Forum Discussion
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:
| 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.
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:
5 Replies
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:
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
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] ) ) )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]))returngetValuethen you can achive your goal i tried this one.please refer the screenshot belowMark it as a solution if it meets your requirement
Thank You!Anonymous If this post helps, please consider accept as solution to help other members find it more quickly.
