IF Value > 0

[Anonymous]

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: 

[v-juanli-msft · Accepted 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

[Maddy99]

Could you open this table in Power BI Query editor?

there you can use the function 'remove duplicates'

[Anonymous]

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

[HotChilli]

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.