Index Match in Dax?

[hayden]

I have created a query from a csv file that results in a dataset named 'Daily Stats' that looks like this:

I've also created a measure to calculate the minimum of the 8ResContents attribute.

The result of that based on the table above would be 499. Now I am trying to create another measure to return the drought stage during this minimum storage.

In excel this would simply be an index and match.. But I haven't been able to figure out how to do this in DAX/PQ.

I'm pretty new at Dax, so if you have the time to help me understand what your solutions is dong that would be wonderful, but not required.

 

[Matt Allington]

This is actually quite tricky in DAX. I’m not at my pc so its hard for me to provide an exact solution.
in short, you need to
filter the table based on the min value
with that filter applied, extract the date
then apply the date filter back on the table to fetch the other number.

you could also pivot the data in PQ first. turn the attribute column into multiple columns If you put a bottomN filter on a visual, it would give the 499 value showing all the other data, too. But that wouldn’t allow you to do more calcs.
if you load a workbook with some sample data, I can take a look when I’m back at my pc.

 

[Matt Allington]

I’ve been thinking this through. You might like to look at lookupvalue() as a solution.

 

[hayden]

@Matt Allington - Thanks for the quick reply. Glad to know it wasn't something super simple that I was missing. I've sent you a private message with a dropbox link to the workbook. If you need the CSV file let me know. I'll read up on lookupvalue()...

I was trying that but it requires a specific column, not an attribute, and I don't think I can pass it a measure as the value to lookup.

But I'll keep looking.

 

[Matt Allington]

@Matt Allington - Thanks for the quick reply. Glad to know it wasn't something super simple that I was missing. I've sent you a private message with a dropbox link to the workbook. If you need the CSV file let me know. I'll read up on lookupvalue()...

I was trying that but it requires a specific column, not an attribute, and I don't think I can pass it a measure as the value to lookup.

But I'll keep looking.

You can definitely pass a measure as the value to look up

 

[hayden]

I duplicated my query and left the data as a table (i.e, attribute as a column header, and a value for each date in each row). With this loaded to the datamodel I was able to use lookupvalue along with measures to get the values I needed.

Not thrilled about having to load my data twice (once as a table, and the same table unpivoted), but for now that will work.

 

[Matt Allington]

@Matt Allington - Thanks for the quick reply. Glad to know it wasn't something super simple that I was missing. I've sent you a private message with a dropbox link to the workbook. If you need the CSV file let me know. I'll read up on lookupvalue()...

I was trying that but it requires a specific column, not an attribute, and I don't think I can pass it a measure as the value to lookup.

But I'll keep looking.

I'm not sure if this is private data or not. I offer support that I can publish publicly so that others can benefit, too. I can't see any reason why you can't use the unpivoted data to do this. If you publicly post some data that I can use that is unpivoted, I will have a go for you.