Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
@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.
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 )
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 )
@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.
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |