Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Icon for Administrator rankAdministrator
3 years ago
Solved

Create new column based on a calculated one

Good

I am trying to create a column that gives me the most repeated value for a certain value. For this I have made a Count column, where I calculate how much the value is repeated.

I need to fill in the column name + repeated, with the name that is repeated the most for each code.

Thank you!

  • mangaus1111's avatar
    mangaus1111
    3 years ago

    Hi Syndicate_Admin ,

    try this column

    Column = 
VAR _MaxCount =
MAXX(
   FILTER(
       'Table',
       'Table'[Code] = EARLIER('Table'[Code])
      ),
'Table'[Count]
)
RETURN
SELECTCOLUMNS(
FILTER(
    'Table',
    'Table'[Code] = EARLIER('Table'[Code]) && 'Table'[Count]  = _MaxCount
),
"Nombre",[Nombre]
)

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

  • v-yinliw-msft's avatar
    v-yinliw-msft
    3 years ago

    Hi Syndicate_Admin ,

     

    You can try this method:

    Name + repeated =
VAR _max =
    CALCULATE ( MAX ( 'Table'[Count] ), ALLEXCEPT ( 'Table', 'Table'[Code] ) )
RETURN
    CALCULATE ( MAX ( 'Table'[Number] ), FILTER ( 'Table', 'Table'[Count] = _max ) )

    The result is:

    Hope this helps you.

    Here is my PBIX file.

     

    Best Regards,

    Community Support Team _Yinliw

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

5 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    Syndicate_Admin , The information you have provided is not making the problem clear to me. Can you please explain with an example.

    Appreciate your Kudos.


    • Syndicate_Admin's avatar
      Syndicate_Admin
      Icon for Administrator rankAdministrator
      3 years ago

      Good

      I have prepared a table to try to explain it. I want the column "name + repeated" to take the value of "Name" that is repeated more times for a code.

      That is, for Code MI-0876 that has three entries, put the value of Peter.

      CodeCountNumberName + repeated
      MI-087610LouisPedro
      MI-087620PedroPedro
      MI-08765AntonioSedro
      MU-0981SofiaSofia
      MU-07814AlejandraAlejandra

      thanks and greetings

      • mangaus1111's avatar
        mangaus1111
        Icon for Solution Sage rankSolution Sage
        3 years ago

        Hi Syndicate_Admin ,

        try this column

        Column = 
VAR _MaxCount =
MAXX(
   FILTER(
       'Table',
       'Table'[Code] = EARLIER('Table'[Code])
      ),
'Table'[Count]
)
RETURN
SELECTCOLUMNS(
FILTER(
    'Table',
    'Table'[Code] = EARLIER('Table'[Code]) && 'Table'[Count]  = _MaxCount
),
"Nombre",[Nombre]
)

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

  • mangaus1111's avatar
    mangaus1111
    Icon for Solution Sage rankSolution Sage
    3 years ago

    Hi Syndicate_Admin ,

    try with this calculated column

    Column = 
VAR _MaxCount = MAX('Table'[Count])
RETURN
SELECTCOLUMNS(
FILTER(
    'Table',
     'Table'[Count]  = _MaxCount
),
"Nombre",[Nombre]
)

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