Compare two Columns

[jp5481]

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

[Icey]

Hi @jp5481 ,

Is this problem sloved?

If it is sloved, could you kindly accept it as a solution to close this case?  

If not, please let me know.  

Best Regards

Icey

[Greg_Deckler]

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

[jp5481]

---

@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.

[Icey · Accepted 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.

[jthomson]

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?

[jp5481]

---

@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.