Finding value in other table and return the latest entry

[p1ngu1n]

Hi all,

I have two tables whereas I want to retreive data from the 2nd to the 1st.

The first table (devices) holds only basic information about the devices, the second (usage) details about the usage of the devices.

I want to get information about in which location the devices was last used to show the devices depending on the selected location.

Thx for the help!

[v-juanli-msft]

Hi @p1ngu1n 

Column =
VAR _date =
    CALCULATE (
        MAX ( Usage[timestamp] ),
        ALLEXCEPT (
            Usage,
            Device[Id]
        )
    )
RETURN
    CALCULATE (
        SELECTEDVALUE ( Usage[location] ),
        Usage[timestamp] = _date
    )

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[p1ngu1n]

Im receiving this error "Function 'CALCULATE' is not allowed as part of calculated column DAX expressions on DirectQuery models."

[p1ngu1n]

@v-juanli-msft Is that a problem of the DirectQuery?

[amitchandak · Accepted solution]

You can get it by creating 2 new columns in the devices table

last time stamp = minx(filter([usages],devices[id]=usages[device_id]),usages[timestamp])
last location = minx(filter([usages],devices[id]=usages[device_id] && usages[timestamp] =devices[last time stamp ] ),usages[location])

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[p1ngu1n]

@amitchandak I guess also here the DirectQuery mode is the issue, as MINX is not allowed because of this.

[amitchandak]

I think Minx is allowed, the new column is not allowed, so we need to build using measures . Can you please share data in tabular format.

[p1ngu1n]

I will realize it with an additional table which is fine for me. As I guess your solution works in import mode I will accept it as answer. 

Thanks for helping me out!

[p1ngu1n]

@amitchandak I suppose you mean measure not column, right? I would not know how to use MINX in a column.

As I would like to use the location as a legend for a visual this does not work for me unfortunately, as measures are not allowed as legend fields for visuals...

[amitchandak]

There are columns

[Anonymous]

@p1ngu1n I assuming there is relationship between device and usage table. device(id)-->usage(device_id)

First approach : - Use location column in table visual(select maximum option by right click on location field) against the id of device table.

or create a measure

Measure = 
VAR _date = CALCULATE(MAX(Usage[timestamp]),ALLEXCEPT(Usage,Device[Id]))
RETURN CALCULATE(SELECTEDVALUE(Usage[location]),Usage[timestamp]=_date)