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 am looking for an answer to the below question I have while preparing some visuals in Power BI. Appreciate your help and valuable advice on this query.
I have data in a table (Users table) as below:
I need to find out the most frequent user based on the duration they have logged into the device. In this table, the most frequent user for the deviceId=1, would be User C. Similarly, the most frequent user for the deviceId=2, would be User Y.
I have another existing table (Devices table) as below and want to add a new column 'mostFrequentUser' (yellow highlighted) to this table which will be the most frequent user for the corresponding devices based on calculations from the Users table.
A solution I found out for this problem is to create a measure variable (Max User) in the the Users table as below.
Difference = DATEDIFF(Users[firstSeen],Users[lastSeen],MINUTE)
Max user =
CALCULATE (
MAX (Users[User] ),
FILTER (
ALL ( 'Users' ),
[Difference] = MAX ( 'Users'[Difference])
&& [deviceID] = SELECTEDVALUE ( Users[deviceID] )
)
)
But, I need to filter some visuals based on the most frequent user and the end user of the report is demanding to add a slicer to filter using the most frequent user in the report. Since, we can not use a measure variable as a slicer, the above measure variable is not doing the job and I would need the above logic to be implemented as a new colum in the 'Devices' table, so that I can add a slicer in the report for the most frequent users.
Could anyone please help me with this.
Thanks in advance for your help.
hi @Anonymous Create a calculated column in the Devices table
Max user =
CALCULATE (
MAX ('UsersTable'[User] ),
FILTER (
'UsersTable',
[Difference] = 'UsersTable'[Difference]
&& [deviceID] = DevicesTable[deviceID]
)
)
Hi @DimaMD ,
To be more specific:
The problem I am facing with this solution is associated with MAX function.
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 |
---|---|
117 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |