How can I remove duplicated values for same category ID

[Anonymous]

Hello everyone !

I am facing an issue with my report right now, I need to calculate efficacity (theorical time divided by real time) for different activities.

The problem is that in order to get the theorical time I need to merge 2 tables (main table & theorical time table) by activity ID. 

As I have different real activities that have the same ID (since it is related to the same activity), my theoretical time is multiplied by the number of rows I have for the same activity ID.

Here is a sample of data that I have in my dataset :

I would then like to be able to obtain the theoretical time associated with my activity ID in the first line of my activity ID for example.

Thank you for the help you can provide me with that topic 🙏

[Anonymous · Accepted solution]

I just found how to convert @Greg_Deckler's measure into calculated column, which reach the result expected.

Here is the formula :

Theorical time column = 
    VAR __Activity = 'Table'[Activity ID]
    VAR __MaxActivity = 
        MAXX(
            FILTER(
                ALL('Table'),
                [Activity ID] = __Activity
            ),
            [real time]
        )
    VAR __TpsTheoAppro = 'Table'[theorical time]
    VAR __Result = IF('Table'[real time] = __MaxActivity, __TpsTheoAppro, BLANK())
RETURN
    __Result

Result is the following :

The theorical time is shown only once per Activity ID.

Thanks again for great help

[Greg_Deckler]

@Anonymous Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[Anonymous]

Hi @Greg_Deckler ,

Thank you for your reply, I just checked among the common issues you shared but unfortunately didn't found it.

Here is a text sample of the data I actualy have :

Activity ID |	real time |	theorical time
1	10	25
1	7	25
2	20	50
1	5	25
2	3	50

The expected result is the following :

Activity ID |	real time |	theorical time
1	10	25
1	7
2	20	50
1	5
2	3

To reach the expected result, I would like to check for each Activity ID if there is more than one row (in the example below: 3 rows for Activity ID n°1, 2 rows for Activity ID n°2. If there is more than 1 row for each Activity ID, I would like to replace the duplicates in the theorical time column for one Activity ID with blank value. 

Hope that my request is more clear now

[Greg_Deckler]

@Anonymous See PBIX attached below signature.

[Anonymous]

@Greg_Deckler it fits my needs perfectly !

But how can I convert this measure as calculated column instead ?

Thank you for the help provided !