cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
chronis69 Member
Member

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

Accepted Solutions
Super User
Super User

Re: Issue with VALUES function

@chronis69 all_partners is a table, not a column.






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

DeeDice Frequent Visitor
Frequent Visitor

Re: Issue with VALUES function

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
Super User
Super User

Re: Issue with VALUES function

@chronis69 all_partners is a table, not a column.






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

chronis69 Member
Member

Re: Issue with VALUES function

@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

DeeDice Frequent Visitor
Frequent Visitor

Re: Issue with VALUES function

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

Highlighted
Super User
Super User

Re: Issue with VALUES function

@chronis69 change all unique partners to

 

DISTINCT(all_partners)





Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





chronis69 Member
Member

Re: Issue with VALUES function

@parry2k @DeeDice thanks a lot !!!

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 243 members 2,443 guests
Please welcome our newest community members: