cancel
Showing results for 
Search instead for 
Did you mean: 
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.






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.






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)





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
Exciting changes

Power BI Community Changes

Check out the changes to the Power BI Community announced at Build.

May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Kudo Data Story carousel

Data Stories Gallery

Visit our Data Stories Gallery and give kudos to your favorite Data Stories.

Top Solution Authors