Forum Discussion

parry2k's avatar
parry2k
Icon for Super User rankSuper User
9 years ago

lookup value based on measure

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

 

 

Need Help

7 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    9 years ago

    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's avatar
      pnvinod
      Icon for Helper I rankHelper I
      9 years ago

      Can you share the measure Definition

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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's avatar
      parry2k
      Icon for Super User rankSuper User
      9 years ago

      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's avatar
        Anonymous
        Not applicable
        9 years ago

        parry2k,

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

        Regards,
        Lydia Zhang