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,
I have the following table, I would like to get the distinct count as a query for TrackerID but when the durationconnected is greater than zero, then pick the TrackerID value for DurationConnected that is greater than zero (see second table). Thanks for any help.
I would like to get the following result:
Solved! Go to Solution.
Hi @Anonymous
You could use DAX to change the data model.
Create a new table
Table =
FILTER (
SUMMARIZE (
ALL ( Sheet2 ),
[duration],
[id],
"rank", RANKX (
FILTER ( ALL ( Sheet2 ), [id] = EARLIER ( Sheet2[id] ) ),
[duration],
,
DESC,
DENSE
)
),
[rank] = 1
)
Best Reagrds
Maggie
Hi @Anonymous
You could use DAX to change the data model.
Create a new table
Table =
FILTER (
SUMMARIZE (
ALL ( Sheet2 ),
[duration],
[id],
"rank", RANKX (
FILTER ( ALL ( Sheet2 ), [id] = EARLIER ( Sheet2[id] ) ),
[duration],
,
DESC,
DENSE
)
),
[rank] = 1
)
Best Reagrds
Maggie
Could you open this table in Power BI Query editor?
there you can use the function 'remove duplicates'
Hi Mandy,
Yes, I know but when I remove the duplicates for TrackerID, it sometimes takes the 'DurationConnected' value of 0 but I want to remove duplicates for 'TrackerID' and if there is a duplicate with a value of >0, then take that value as shown in the second table.
Thanks
You'll want to remove duplicates of the row, not just the column TrackerID, so make sure both columns are selected first.
The M for this is = Table.Distinct(Source)
If your real data follows the pattern of your sample, you could then group by the TrackerID and MAX the DurationConnected.
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 |
---|---|
111 | |
94 | |
83 | |
67 | |
59 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |