Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

IF Value > 0

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 DurationCon...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    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's avatar
Maddy99
Resolver II
7 years ago

Could you open this table in Power BI Query editor?

there you can use the function 'remove duplicates'

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      HotChilli
      Community Champion
      7 years ago

      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.