How to use the unique items from in a column from several tables as the row criteria of a pivot table in excel data model using Dax?

1
votes

I have 3 different tables with the customer name and there are duplicates as well as unique customers in the 3 tables and I need to get the unique for all 3 to be used as the rows criteria in the pivot table.

I've been finding a way to do so but I cannot seem to figure it out.

The measure I tried is: Customers:=DISTINCT(UNION(VALUES('Test1 - Invoice'[CustomerID]),VALUES('Test2 - Invoice'[CustomerID]),VALUES('Test3 - Invoice'[CustomerID])))

But I get the error below:

Semantic Error: Too many arguments were passed to the VALUES function. The maximum argument count for the function is 1.

I am quite new to DAX and have no idea how to do it. I believe it is because measures are only for values if i'm not mistaken

I read that to place on other fields of the pivot table, it has to be a calculated column although I do not see how it can be a calculated column as well.

1
excelpowerbidax
If I remember correctly, UNION function only takes 2 tables. You can solve this issue by using variables (union 2 tables first, then union the result with the third table). On a separate note, I would revisit your data model - in a proper design, you shouldn't have problems like that (why do you have customers in 3 places?) – RADO
@RADO we have a separate file for each different company, but functions similarly enough and have overlaps of customers that we can join the tables to do a holistic analysis. Am I right to use a measure though? And is the formula okay as it is besides the UNION portion? – Pherdindy
If you want to use the result as rows in a pivot table, then you are on a wrong path (measures can not be used that way). What you need to do is to design a proper star schema where all customers are in one master table. How to do it is a separate question. Also, data transformation tasks are usually done in Power Query, not in DAX (or better yet, in a data warehouse). DAX is a reporting language, not suitable for data processing. – RADO
@RADO thanks that makes sense. I was just thinking to minimize the worksheets hanging around but I suppose i'll need to use some VBA/power query to automate it – Pherdindy

1 Answers

1
votes

One approach is to create a separate table to store the Customer Name dimension - then create relationships between that Customer dimension table and your 3 fact tables. This would be most effective at the Power Query stage, but can be done using DAX.

An alternative is to merge your 3 fact tables - again, this would be best done with Power Query, but is possible with DAX.