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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Issue with VALUES function

Hi

 

I have a problem with the the VALUES function. I have two tables, one with Azure sales and one with Office 365 sales. Each table has a column with the name of a partner. I want to produce a consolidated table with all the unique partner names. 

 

As expected, most partners transact in both tables but not all. I use the VALUES function to get the unique partners in each table and then the UNION function to bring them together in a single table. all good until here.

 

then I use the VALUES function in this new table but VALUES complain. I try to do that in a single table definition so as to reduce the number of tables that I create in my model. below is a screenshot of my code. 

Am i doing something wrong, or is this an expected DAX behaviour?

 

Picture1.png

 

2 ACCEPTED SOLUTIONS
parry2k
Super User
Super User

@Anonymous all_partners is a table, not a column.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

Anonymous
Not applicable

Hi Chronis69

 

You need to replace the last VALUES with DISTINCT:

 

Table=

VAR Azure = VALUES(yourColumn)

VAR O365 = VALUES(yourColumn)

VAR allpartners = UNION (Azure, O365)

VAR distinctPartners DISTINCT(allpartners )

RETURN  distinctPartners 
 

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

Hi Chronis69

 

You need to replace the last VALUES with DISTINCT:

 

Table=

VAR Azure = VALUES(yourColumn)

VAR O365 = VALUES(yourColumn)

VAR allpartners = UNION (Azure, O365)

VAR distinctPartners DISTINCT(allpartners )

RETURN  distinctPartners 
 
parry2k
Super User
Super User

@Anonymous all_partners is a table, not a column.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@Anonymous change all unique partners to

 

DISTINCT(all_partners)


Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

@parry2k @Anonymous thanks a lot !!!

Anonymous
Not applicable

@parry2k thanks.. !!!!

so how do I pick the one and only column in the partners table? I tried using the '[' to try to get autocomplete to kick in but no columns appeared

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.