Forum Discussion
Nagasai
4 years agoFrequent Visitor
How to filter a Column having concatenated values with values selected in slicer
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 value...
tamerj1
Community Champion
4 years agoHi 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!