Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
mina97
Helper III
Helper III

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 
1 ACCEPTED SOLUTION
ERD
Super User
Super User

@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:

ERD_2-1693903240076.png

 

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

Check out my latest demo report in the data story gallery.

Stand with Ukraine!


Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

View solution in original post

8 REPLIES 8
ERD
Super User
Super User

@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:

ERD_2-1693903240076.png

 

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

Check out my latest demo report in the data story gallery.

Stand with Ukraine!


Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

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

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

ERD
Super User
Super User

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

ERD_1-1693902610102.png

 

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

Check out my latest demo report in the data story gallery.

Stand with Ukraine!


Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

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

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

mina97
Helper III
Helper III

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 

SamInogic
Super User
Super User

Hi @mina97 ,

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

SamInogic_0-1693898844401.png

 

Then you can design chart with below specifications,

SamInogic_1-1693898851968.png

 

Thanks!

 

Inogic Professional Services Division

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

Drop an email at crm@inogic.com

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

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors