Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

How can I remove duplicated values for same category ID

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 🙏

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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

5 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    3 years ago

    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's avatar
      Anonymous
      Not applicable
      3 years ago

      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
      11025
      1725
      22050
      1525
      2350

       

      The expected result is the following :

      Activity ID |real time |theorical time
      11025
      17 
      22050
      15 
      23 

       

      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

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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