Forum Discussion

mina97's avatar
mina97
Helper III
2 years ago
Solved

unique vales based on

i have the following data 

 

Fisrt namelast nameemaildegreepayment ID
jackEden Eden exapmle.combachelor250$2160003021
loraTeigen Teigen example.combachelor3403160003021
jousefHawke Hawke example.combachelor1004160003021
jackEden Eden1exapmle.combachelor500$5160003021

and i want a bar graph to show me how much each one takes based in ids 

y-axis : first name

x-axis: sum of payment 

i wrote the following code but it mixes the jacks and i need uniqe jack only 

 

new name =
var a = COUNTROWS(FILTER(DISTINCT('Specialists'[ID]),'Specialists'[ID]<=EARLIER([ID])&&'Specialists'[First_Name]=EARLIER([First_Name])&&'Specialists'[Last_Name]=EARLIER([Last_Name])&&'Specialists'[ Email]=EARLIER([ Email])&&'Specialists'[ degree]=EARLIER([ degree])))
return SWITCH(a,1,'Specialists'[First_Name],2,'Specialists'[First_Name]&" ",3,'Specialists'[First_Name]&"  ",4,'Specialists'[First_Name]&"    ",5,'Specialists'[First_Name]&"     ",'Specialists'[First_Name]&"     ")
 
please help me and thank you 
  • ERD's avatar
    ERD
    2 years ago

    mina97 , I now get what you wanted in your code. 

    So, create a calculated column in your table:

    name unique =
VAR same_names =
    CALCULATETABLE (
        SUMMARIZE ( Specialists, Specialists[ID], Specialists[First_Name] ),
        ALLEXCEPT ( Specialists, Specialists[First_Name] )
    )
VAR ranking = RANKX ( same_names, Specialists[ID],, ASC, DENSE )
VAR blanks = REPT ( UNICHAR ( 8204 ), ranking - 1 )
VAR res = Specialists[First_Name] & blanks
RETURN
    res

    And use this column for the x-axis of your visual:

     

8 Replies

  • ERD's avatar
    ERD
    Community Champion
    2 years ago

    mina97 , I now get what you wanted in your code. 

    So, create a calculated column in your table:

    name unique =
VAR same_names =
    CALCULATETABLE (
        SUMMARIZE ( Specialists, Specialists[ID], Specialists[First_Name] ),
        ALLEXCEPT ( Specialists, Specialists[First_Name] )
    )
VAR ranking = RANKX ( same_names, Specialists[ID],, ASC, DENSE )
VAR blanks = REPT ( UNICHAR ( 8204 ), ranking - 1 )
VAR res = Specialists[First_Name] & blanks
RETURN
    res

    And use this column for the x-axis of your visual:

     

    • mina97's avatar
      mina97
      Helper III
      2 years ago

      IT WORKS perfectly THANK YOU SO MUCH!!!! you are a lifesaver

    • mina97's avatar
      mina97
      Helper III
      2 years ago

      hi please i need extra help i reallt appreciate it 

      how can i assign uniqe ID to the most frequent first name

      because i have a lot of misspelled names and i want to unifiedthem
      i have the following data 
       

      Fisrt namelast nameemaildegreepayment ID
      jackEden Eden exapmle.combachelor250$2160003021
      loraTeigen Teigen example.combachelor3403160003021
      jackEdinEden1exapmle.combachelor600$5160003021
      jackEden Eden1exapmle.combachelor500$5160003021
  • SamInogic's avatar
    SamInogic
    Super User
    2 years ago

    Hi mina97 ,

    Instead of custom column with DAX expression, you can try creating Index column within Power Query in order generate the unique numbers,

     

    Then you can design chart with below specifications,

     

    Thanks!

     

    Inogic Professional Services Division

    Power Platform and Microsoft Dynamics 365 CRM Development – All under one roof!

    Drop an email at [email protected]

    Services:  http://www.inogic.com/services/

    Power Platform/Dynamics 365 CRM Tips and Tricks:  http://www.inogic.com/blog/

  • mina97's avatar
    mina97
    Helper III
    2 years ago

    hi SamInogic  i appreciate your help 

    but jack who's id is 2160003021 had 2 payemnts 250$ and 600$ as 2 seperated rows but identical columns inputs except for payment column 

    and i need the graph to show that this jack got 850$ as a total payment hence, the index method is not useful in this case. 

    please help me and Thank you for your response 

  • ERD's avatar
    ERD
    Community Champion
    2 years ago

    mina97 , you already have a unique field - ID. Can you use it in the visual?

     

    • mina97's avatar
      mina97
      Helper III
      2 years ago

      unfortunately no i can not show id in graph 😞 that is why i need help with this 

    • mina97's avatar
      mina97
      Helper III
      2 years ago

      please find a way to show my results based on id with out showing id on graph