Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |