cancel
Showing results for
Did you mean:
Highlighted
Regular Visitor

## 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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support

## Re: Compare two Columns

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 =
__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.

6 REPLIES 6
Highlighted
Super User I

## Re: Compare two Columns

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?

Highlighted
Super User IX

## Re: Compare two Columns

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```

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Highlighted
Regular Visitor

## Re: Compare two Columns

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

Highlighted
Regular Visitor

## Re: Compare two Columns

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

Highlighted
Community Support

## Re: Compare two Columns

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 =
__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.

Highlighted
Community Support

## Re: Compare two Columns

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