Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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
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.
Im receiving this error "Function 'CALCULATE' is not allowed as part of calculated column DAX expressions on DirectQuery models."
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
@amitchandak I guess also here the DirectQuery mode is the issue, as MINX is not allowed because of this.
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.
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!
@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...
There are columns
@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)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |