Forum Discussion
Compare two Columns
Guys, I have tried hard to figure out a formula to do this, but I'm unsuccessful. I need to compare two columns in a single table, and produce the results which follow. I don't know if I have a preference that it be a calculated column vs. a measure:
ProductsCustomers
| product 1 | customer 1 |
| product 2 | customer 1 |
| product 1 | customer 1 |
| product 2 | customer 2 |
| product 3 | customer 2 |
| product 4 | customer 1 |
| product 2 | customer 2 |
| product 1 | customer 1 |
| product 4 | customer 1 |
CustomersProductsNum Purchases
| Customer 1 | Product 1 | 3 |
| Customer 1 | Product 2 | 1 |
| Customer 1 | Product 3 | null |
| Customer 1 | Product 4 | 2 |
| Customer 2 | Product 1 | null |
| Customer 2 | Product 2 | 2 |
| Customer 2 | Product 3 | 1 |
| Customer 2 | Product 4 | null |
Thanks in advance
Hi jp5481 ,
You can modify Greg_Deckler 's DAX expression like this:
Table 10 = VAR __table = GENERATEALL ( DISTINCT ( SELECTCOLUMNS ( 'Table', "__customer", [Customers] ) ), DISTINCT ( SELECTCOLUMNS ( 'Table', "__product", [Products] ) ) ) VAR __table1 = ADDCOLUMNS ( __table, "number", COUNTROWS ( FILTER ( 'Table', [Products] = [__product] && [Customers] = [__customer] ) ) ) RETURN __tableBest Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
Not sure why you can't just put both the columns in the row section of the matrix, and have the values be a count of the rows?
jthomson wrote:Not sure why you can't just put both the columns in the row section of the matrix, and have the values be a count of the rows?
You are correct. However, I can't get what I really wanted this way -- I wanted to data to filter down to only who me customers that purchased multiple products, and to display the product names. Thank you for the suggestion.
Here is a DAX table expression for it:
Table 10 = VAR __table = GENERATEALL(DISTINCT(SELECTCOLUMNS('Table 9',"__product",[Products])),DISTINCT(SELECTCOLUMNS('Table 9',"__customer",[Customers]))) VAR __table1 = ADDCOLUMNS(__table,"number",COUNTROWS(FILTER('Table 9',[Products]=[__product] && [Customers]=[__customer]))) RETURN __table1
Greg_Deckler wrote:Here is a DAX table expression for it:
Table 10 = VAR __table = GENERATEALL(DISTINCT(SELECTCOLUMNS('Table 9',"__product",[Products])),DISTINCT(SELECTCOLUMNS('Table 9',"__customer",[Customers]))) VAR __table1 = ADDCOLUMNS(__table,"number",COUNTROWS(FILTER('Table 9',[Products]=[__product] && [Customers]=[__customer]))) RETURN __table1You are correct. This expression produces the intended results. However, what I was hoping to get was a result that only displayed customers who purchased multiple products, along with a list of the products they purchased. I don't know how to ask that question properly.
Hi jp5481 ,
You can modify Greg_Deckler 's DAX expression like this:
Table 10 = VAR __table = GENERATEALL ( DISTINCT ( SELECTCOLUMNS ( 'Table', "__customer", [Customers] ) ), DISTINCT ( SELECTCOLUMNS ( 'Table', "__product", [Products] ) ) ) VAR __table1 = ADDCOLUMNS ( __table, "number", COUNTROWS ( FILTER ( 'Table', [Products] = [__product] && [Customers] = [__customer] ) ) ) RETURN __tableBest Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
