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 DurationConnected that is greater than zero (see second table).  Thanks for any help. 

 

I would like to get the following result: 

 

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

4 Replies

  • Maddy99's avatar
    Maddy99
    Icon for Resolver II rankResolver 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
        Icon for Community Champion rankCommunity 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.

         

         

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    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