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.
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
Solved! Go to Solution.
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 __table
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @jp5481 ,
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 __table1
You 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 __table
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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.
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 |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |