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

[dinosainsburys]

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:

Computer	RESULTS.DATE.0	RESULTS.DATE.1	RESULTS.DATE.2	xxxxx	RESULTS.VALUE.0	RESULTS.VALUE.1	xxx
COMP-4573	12/12/2023	13/12/2023	14/12/2023	xxxx	54638	5699	xxxx
xxxx	xxxx	xxx	xxxx	xxx	xxxx	xxxx	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

Computer	RESULTS.DATE	RESULTS.VALUE
COMP-4573	12/12/2023	58.1
COMP-4573	12/12/2023	58.12
COMP-4573	12/12/2023	58.5
COMP-4573	12/12/2023	57.8
COMP-4573	13/12/2023	60
COMP-4573	13/12/2023	61.2
COMP-4573	13/12/2023	61.2
xxx	xxxxx	61.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?

[v-heq-msft]

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]

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! 🙂

[v-heq-msft · Accepted solution]

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

[AUDISU]

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