Dax custom measure based on column value - can not access field name in measure

[dannn777]

Hi, i'm trying to create a custom measure to display a value based on the value of the source column.

However, i can not access the source column in DAX so i have no idea how to progress.

i'm connected live to SSAS and the target column is not calculated or transformed in any way, which I assume is why I can not access the column.

The column contains values such as 121212Sales, 34234234Sales, 428282Marketing, 238293223Marketing, and I'd like the measure to be based on this value, e.g. if left 2 characters are 12 then Sales, if left 2 characters of the value are 23 then Marketing etc.

[v-yuta-msft · Accepted solution]

Hi dannn777,

Are you using import mode or live connection mode? If you are using import mode, the way Seward12533 has mentioned is better, you can create a lookup table and create a relationship between two tables based on a related column such as index column. If you are using live connection mode and the database is tabular you can create a measure using DAX like pattern below:

Category = SWITCH(LEFT(MAX(Table1[Values]), 2), "12" , "Sales", "34", "Sales", "42", "Marketing", "23", "Marketing")

 

Regards,

Jimmy Tao

[dannn777]

Hi Jimmy,

it is a live connection.

i seem to have solved it by implementing your solution. However could not use MAX as some values were strings and was throwing an error. After some searching I replaced MAX with LASTNONBLANK and this seems to work.

switch = SWITCH(LEFT(LASTNONBLANK(Table[Value],0), 2), "10" , "Sales", "20", "Sales", "42", "Marketing", "23", "Marketing")

[v-yuta-msft]

Hi dannn777,

Good to hear that.

Regards,

Jimmy Tao

[dannn777]

Hi Jimmy

sorry I have one more question.

i do not seem to be able to use this new measure as a "value" in a table without bringing in the original column.

e.g.

switch measure with original column

switch measure only

Is there a way i can use this measure as a column with other data, will this require a lookup table?

[Seward12533]

Add a caluclated column to the table using LEFT([column],2) then make a lookuptable that Maps these to the the values you want to display and setup a relationship between the two tables.