Forum Discussion

dinosainsburys's avatar
dinosainsburys
Helper I
2 years ago
Solved

How do I remove duplicate values within a specific category of rows

I ingest data from the Dynatrace API in relation to the drive utilisation of servers. In order to get it to a readable state, I had to Unpivot columns as it was presented like this when it came through:

 

ComputerRESULTS.DATE.0RESULTS.DATE.1RESULTS.DATE.2xxxxxRESULTS.VALUE.0RESULTS.VALUE.1xxx
COMP-457312/12/202313/12/202314/12/2023xxxx546385699

xxxx

xxxxxxxxxxxxxxxxxxxxxxxxxx

xxxx

 

 

The RESULTS.DATE (they were in EPOCH Time but did some transformations to get it to a friendly date) columns would repeat for at least 30 times and so would the RESULTS.VALUE column. They both were Unpivoted to combine the two categories together. So now the Date and Results column are in one belonging to their respective category. 

 

The issue I am having is that there is a lot of duplicate dates for the same 'Computer' but the Results column is different too. So it looks like this

 

ComputerRESULTS.DATERESULTS.VALUE
COMP-457312/12/202358.1
COMP-457312/12/202358.12
COMP-457312/12/202358.5
COMP-457312/12/202357.8
COMP-457313/12/202360
COMP-457313/12/202361.2
COMP-457313/12/202361.2
xxxxxxxx61.3

 

If I put the visualisation as a table, it is appararent the results value is different and there is variation

Is there a sort of measure or DAX expression which collates the date as one value with the results value? Or to only take in one value from the date column?

Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi dinosainsburys ,
    According to your description, in order to make the final generated data more meaningful, you can try to use the average value, maximum value(Max), minimum value(Min) as a measure for a certain day, and you can do this by modifying the parameter referenced after "Total Value". Here is an example of an average value

    Total Value by Date = 
CALCULATETABLE(
    ADDCOLUMNS(
        DISTINCT('Table'[RESULTS.DATE]),
        "Computer",MAX('Table'[Computer]),
        "Total Value", CALCULATE(AVERAGE('Table'[RESULTS.VALUE]),ALLEXCEPT('Table','Table'[RESULTS.DATE]))
    ),
    'Table'[RESULTS.DATE] = 'Table'[RESULTS.DATE]
)

     

     

    Best regards,

    Albert He

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

4 Replies

  • AUDISU's avatar
    AUDISU
    Resolver III
    2 years ago

    Hi dinosainsburys ,
    Create a calender table and create relationship between calender table and this table. Then use calender date column to visual.
    If not try to change aggrigation type to First in Result.Date column in your visual table.


    Thanks

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi dinosainsburys ,
    Thanks to AUDISU @ for the solution. Next is what I need to add:
    First you can manipulate the data by using matrix visual objects:

    Secondly you can create a new column above the original table to merge the two columns.

    Column = CONCATENATE(FORMAT('Table'[RESULTS.DATE],"dd-mm-yyyy"),FORMAT('Table'[RESULTS.VALUE],"+00.00"))

     

    Finally you can also create a new table via dax

    Total Value by Date = 
CALCULATETABLE(
    ADDCOLUMNS(
        DISTINCT('Table'[RESULTS.DATE]),
        "Computer",MAX('Table'[Computer]),
        "Total Value", CALCULATE(SUM('Table'[RESULTS.VALUE]),ALLEXCEPT('Table','Table'[RESULTS.DATE]))
    ),
    'Table'[RESULTS.DATE] = 'Table'[RESULTS.DATE]
)

     

    Best regards,

    Albert He

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

    • dinosainsburys's avatar
      dinosainsburys
      Helper I
      2 years ago

      Hi, this seems almost correct, but this is in relation to drive utilisation of a server drive. So anything above 100 wouldn't make sense. Any way this could be modified, please?

       

      Thanks for the help so far! 🙂

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi dinosainsburys ,
        According to your description, in order to make the final generated data more meaningful, you can try to use the average value, maximum value(Max), minimum value(Min) as a measure for a certain day, and you can do this by modifying the parameter referenced after "Total Value". Here is an example of an average value

        Total Value by Date = 
CALCULATETABLE(
    ADDCOLUMNS(
        DISTINCT('Table'[RESULTS.DATE]),
        "Computer",MAX('Table'[Computer]),
        "Total Value", CALCULATE(AVERAGE('Table'[RESULTS.VALUE]),ALLEXCEPT('Table','Table'[RESULTS.DATE]))
    ),
    'Table'[RESULTS.DATE] = 'Table'[RESULTS.DATE]
)

         

         

        Best regards,

        Albert He

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly