Forum Discussion

jp5481's avatar
jp5481
Regular Visitor
6 years ago
Solved

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 pre...
Need Help
  • Icey's avatar
    Icey
    6 years ago

    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.

Greg_Deckler's avatar
Greg_Deckler
Community Champion
6 years ago

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's avatar
jp5481
Regular Visitor
6 years ago
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's avatar
    Icey
    Community Support
    6 years ago

    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.