Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jp5481
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
Icey
Community Support
Community Support

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

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
Super User
Super User

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...


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

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.

jthomson
Solution Sage
Solution Sage

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.