cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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 1customer 1
product 2customer 1
product 1customer 1
product 2customer 2
product 3customer 2
product 4customer 1
product 2customer 2
product 1customer 1
product 4customer 1

 

CustomersProductsNum Purchases

Customer 1Product 13
Customer 1Product 21
Customer 1Product 3null
Customer 1Product 42
Customer 2Product 1null
Customer 2Product 22
Customer 2Product 31
Customer 2Product 4null

 

Thanks in advance

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
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 =
    ADDCOLUMNS (
        __table,
        "number", COUNTROWS (
            FILTER ( 'Table', [Products] = [__product] && [Customers] = [__customer] )
        )
    )
RETURN
    __table

compare.PNG

 

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
Highlighted
Super User I
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
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

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

@ me in replies or I'll lose your thread!!!

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




Did I answer your question? Mark my post as a solution!

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
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 =
    ADDCOLUMNS (
        __table,
        "number", COUNTROWS (
            FILTER ( 'Table', [Products] = [__product] && [Customers] = [__customer] )
        )
    )
RETURN
    __table

compare.PNG

 

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted
Community Support
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

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors