lookup value based on measure

[parry2k]

hello,

I have a measure in a table which is working great, now based on that measure, I want to look up value in another table. How I can do this?

Assume my measure value is 1 to 100 and there is another table, let's call it "lookup table" which has id from 1 to 100 and description 

So I want to look my measure in this "lookup table", id field, and get description.

Thanks in advance.

P

[Anonymous]

@parry2k,

You can create the calculated column in your original  table using DAX below. Please note that there is no relationship between the lookup table and original table.

Column = CALCULATE(FIRSTNONBLANK(lookup[description],""),FILTER(lookup,lookup[id]=[Measure]))

If the above dax doesn't solve your issue, please share sample data of your table for us to analyze.

Regards,
Lydia

[parry2k]

Hi Lydia,

Thanks for the reply and unfortunately solution you provided is not working. here are more details. 

Here is one of the measure which I'm using to get factor from a non relation table and it is working fine

Factor = 
var myTotalPoints = [Total Points]

return
CALCULATE
(
	VALUES('Score Point'[Factor]), 
	FILTER
	('Score Point', 
		'Score Point'[Measure] = "Factor" &&
		myTotalPoints >= 'Score Point'[Start Range] && 
		myTotalPoints <= 'Score Point'[End Range]
	)
)

here is the screen shot of "Factor" measure from above DAX

Now based on this "Factor" measure I want to create a calculated column to get value from another non-related table, reason I need calculated column, becuase I want to categorize it as a "image url" to show the image for each "Factor". 

So I added new calculated column called "Image" with following DAX and getting same value for each "Factor", here is dax for "image 1" column

image 1 = 
CALCULATE(FIRSTNONBLANK(Images[Image 1],""),FILTER(Images,Images[Factor]=[Factor]))

and I tried the same with measure and it worked but I cannot change the category for a measure

here is DAX for measure 

measure = if( [Factor] = BLANK(), "Blank", LOOKUPVALUE(Images[Image 1],Images[Factor],[Factor]))

now if you see the result for "image 1" which si calculated column and "measure" which is a measure, for "image 1" i'm getting the same value but for "measure" i'm getting correct value, screen shot  below

[Anonymous]

I've the same problem, the measure gets a image url but I cannot assign the "Data Category" to it and I cannot view the image.

Some suggestions.

Ramon

[Anonymous]

Me tree. Really need some excel type kpi icons/indicators.

[Anonymous]

@parry2k,

Could you please share sample data of these tables so that I can test?

Regards,
Lydia Zhang

[parry2k]

Just to add more details, i know how to do the lookup, if I add a new measure with lookup, it works but for the returned measure I cannot change the "Data Category" and my original question was to add as a column so that I can change the "Data Category" since you cannot change category for a measure.

[pnvinod]

Can you share the measure Definition