How to filter a Column having concatenated values with values selected in slicer

[Nagasai]

Hi,

I am looking for a help on Scenario where in on selection of value in slicer it has to filter a column which is having concatenated values.

Scenario:
I have a slicer holding BusinessLine values as below
MVI
CPR
SCI
CPD
I have a fact table having OpportunityID, BusinessLineConcatenated, Amount columns with data as below
12345 MVI,SCI 10000
23456 CPR 25000
45678 MVI 13790
12365 SCR,CPD 54000
67890 VTS,MVI 33000

Requirement:
When we choose MVI in slicer it have to look for selected value in BusinessLineConcatenated and display the sum of Amount for the matching rows
Slicer selection: MVI
Required Output:
10000
13790
33000

total display value = 56790

I have tried using ContainsString in Measure as below but it is working only when 1 value selected
CONTAINSSTRING(Opportunities[BusinessLineConcatenated - Copy],SELECTEDVALUE(Dim_BL[BusinessLine]))

CONTAINSSTRING(Opportunities[BusinessLineConcatenated - Copy],VALUES(Dim_BL[BusinessLine])) is not working. throwing error as multiple values are passed where single value is expected.

We need help on DAX for achieving all below scenarios

• Default it has to show all values
• Single value selection 
• Multiple selections like MVI or CPD 
• When no value is selected means by default it has to show all values when nothing is selected

Any help in achieving this is highly appreciated as we have a delivery this weekend and struck with this issue.

Thank you

[tamerj1]

Hi @Nagasai 
Here is the file with the solution https://www.dropbox.com/t/6K5MESKQs8A8aSH0
Your report looks like this

I used power query to split the concatinated column into 2

Then replaced null values in the 2nd column with the values from the 1st one

You should have a table of all unique Business Lines. Then create the relationships

Finaly create you measure

Amount = 
VAR Amount1 =
    SUM ('Fact'[Amount] )
VAR Amount2Table =
    CALCULATETABLE ( 'Fact', USERELATIONSHIP ( BusinessLine[BusinessLine],'Fact'[BusinessLine.2] ) ) 
VAR Amount2 =
    SUMX (
        Amount2Table,
        IF ( 
            'Fact'[BusinessLine.1] <> 'Fact'[BusinessLine.2],
            'Fact'[Amount] 
        )
    )
VAR Result =
    IF (
        COUNTROWS ( ALLSELECTED (BusinessLine[BusinessLine] ) ) = 1,
        Amount1 + Amount2  
    )
RETURN
    Result

 ihave to note that this works only for one selection. In case of multiple selection the values will blanked out.
Please check and let me know.
Thank you!

[Nagasai]

Thank you @tamerj1 thank you for taking time and providing solution. I will implement it and get back to you on it.

[tamerj1]

@Nagasai 
Sorry, just noticed the file was not updated in my dropbox. Please re-download or use this link https://www.dropbox.com/t/8vRiWoWu8TOTwewb

[tamerj1]

Hi @Nagasai 

I think it is doable. The only thing which I don't know how to do is how extract the required numbers out of the string using DAX. If using power query is ok with you then all is ok to proceed. 
would be great if you can share a sample file