DAX Measure error - to remove fixed values with table column fields

[Anonymous]

Hi Experts

The following measure currently has fixed values i want to changes thes to table column fields.

Current MEasure

Measure 2 = 
VAR _CountryStore = "(Cananda),(Italy);[004],[005]"
VAR SeparatorPosition =
    SEARCH ( ";", _CountryStore )
VAR _Length =
    LEN ( _CountryStore )
VAR _Country =
    LEFT ( _CountryStore, SeparatorPosition - 1 )
VAR _Store =
    MID ( _CountryStore, SeparatorPosition + 1, _Length - SeparatorPosition )
RETURN
    CONCATENATE ( _Country, CONCATENATE ( ";", _Store))

I want to replace 

VAR _CountryStore = "(Cananda),(Italy);[004],[005]"

With pg_control[Country] where teh above is(Cananda),(Italy)

ps_control[store] where[004],[005]

as opposited to fixed values in the variable

[Anonymous]

yes..hold on uploading a sample file

I want the measure to change dynamically based on slicer selected - se measure 2 in the sample 

https://www.dropbox.com/s/vwarlltsrk7s5nq/Sample%20Data.pbix?dl=0 

[johnt75 · Accepted solution]

You could try something like

Selected countries and stores =
VAR _Countries =
	"( "
		& CONCATENATEX(
			VALUES( 'pg_control_cases'[country_dim.country_name] ),
			'pg_control_cases'[country_dim.country_name],
			","
		)
		& " )"
VAR _Stores =
	"( "
		& CONCATENATEX(
			VALUES( 'pg_control_cases'[bu_code] ),
			'pg_control_cases'[bu_code],
			", "
		)
		& " )"
RETURN
	_Countries & ";" & _Stores

[Anonymous]

Hi Johnt75 - last question 
https://community.powerbi.com/t5/Desktop/DAX-measure-error-need-to-seperate-Country-and-stores/m-p/3...

Small edit to the DAX please

[Anonymous]

perfect

[johnt75]

Why do you need to split them if they are already in separate columns ?

[Anonymous]

My question is how do i make the following dynamic based on slicer selction not fixed value in 

VAR _CountryStore = "(Cananda),(Italy);[004],[005]"

The Country values come from 

With pg_control[Country] and store from 

ps_control[store]

[johnt75]

You can use SELECTEDVALUE

VAR _Country =  SELECTEDVALUE('pg_control'[Country])
VAR _Store = SELECTEDVALUE('ps_control'[store])

[Anonymous]

Tried selected value but it goes wrong from here onwards

VAR SeparatorPosition =
    SEARCH ( ";", _CountryStore )
VAR _Length =
    LEN ( _CountryStore )
VAR _Country =
    LEFT ( _CountryStore, SeparatorPosition - 1 )
VAR _Store =
    MID ( _CountryStore, SeparatorPosition + 1, _Length - SeparatorPosition )
RETURN
    CONCATENATE ( _Country, CONCATENATE ( ";", _Store))

[johnt75]

I don't understand. You say that the country and store values are coming from separate columns, what is populating the slicer ?

[Anonymous]

See attached sample file 

i want measure 2 to be dynamic based on slicer selection

https://www.dropbox.com/s/vwarlltsrk7s5nq/Sample%20Data.pbix?dl=0