Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@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
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
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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
Me tree. Really need some excel type kpi icons/indicators.
@parry2k,
Could you please share sample data of these tables so that I can test?
Regards,
Lydia Zhang
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.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Can you share the measure Definition
User | Count |
---|---|
96 | |
87 | |
78 | |
72 | |
69 |
User | Count |
---|---|
113 | |
105 | |
84 | |
65 | |
64 |