Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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
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")
Hi dannn777,
Good to hear that.
Regards,
Jimmy Tao
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.
Is there a way i can use this measure as a column with other data, will this require a lookup table?
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |