Looking for a suggestion on how to grab a value from a table and throw it in a column
In the visual I have attached, I want to add a column to the end of the visual that will grab the current meter reading based on the Service order number
I have created several measures that work well except this one below.
Hour Meter = LOOKUPVALUE(‘Hour Meters’[Current Meter Reading], ‘Hour Meters’[Service Order No.], “SVO12074” )
Needless to say I don’t want to hard code the value. I have only hardcoded it as test to see if I could grab the value for SVO12074. It was successful and pulled in the meter reading of 719. But, it didn’t play well with the rest of the rows of data. The issue I am having is that when I try to add a third argument to the LOOKUPVALUE function, it will only take a scalar value. I have also added my data model which may be suspect. I have also included the hour meter table so whomever looks at this may see the data.
Any help would be appreciated. I’m almost there, I am sure its something simple. Maybe I should be using another Dax function instead.
Hi @lomorris. Can you post your work-to-date in a PBIX (with sample data , if necessary) and a mock-up of your desired outcome (for one or two entries, if that helps)?
Greg
Just thought … your data model may be restricting what you can retrieve from your [Hour Meters] table; I see its 1:*m, but on what fields are the [Posted Invoices] and [Hour Meters] tables linked?
They are linked on Service Order Number. My thinking is the same as you suggested, I am still working at making more solid data models and I do think this is my problem.
Seeing as your already joining on [Service Order Number], one would expect that just dropping the field would work; LOOKUPVALUE should really only be needed for retrieving fields from unrelated tables…
Hi @lomorris. I don’t think it’s the meter reading that’s causing the problem, but perhaps the calculation of your costs. I started to rebuild your desired table from scratch, and everything looked right (including the meter reading)
When I added placeholders for costs, things began to look screwy. Try to remove the last 4 cost fields, and see if the meter reading now comes in correctly. I’ve got other commitments now, so may not be able to get back to you until tomorrow morning. In the meantime, here’s my work-in-progress.
Greg eDNA Forum - Equipment Service History.pbix (1.1 MB)
Thanks Greg for your support on this. From a high-level it looks like I am overthinking my approach and making it harder than it has to be. Will get back to you shortly after I have reviewed your updates. Still new at this and continuing to learn.
Greg,
Was following what you had set up and ran into this issue. I took a look at the hour meter table in the query editor and there are duplicate SVO numbers as there are duplicate SVO numbers in the Posted Invoice table. My understanding is the is a many to many relationship and that is why I see the message, and that makes sense. What is interesting is that in the file I sent you and you sent back it seems that it allows the 1 to * relationship. Not sure what I am missing here. I am going to dig into the data and see if there are duplicate SVO numbers in the hour meter table that match multiple in the posted invoices table.
