Forum Discussion

matsahiro's avatar
matsahiro
Helper II
5 years ago
Solved

DAX Code to Show Data Changes

I'm trying to display top performing products where the price is greater than 50 and the quantity sold is greater than 60. The table is only supposed to display our products that meet this requiremen...
  • CNENFRNL's avatar
    CNENFRNL
    5 years ago

    matsahiro , your question is interesting and the solution is tricky.

    First of all, it's for sure that items in Product column is unique since it's an aggregated monthly sales report. Thus, a 1:1 relationship can be created between any of two reports this way,

     

    Accordingly, the measure is

    Dropped / Added = 
SWITCH (
    TRUE (),
    ISEMPTY ( CALCULATETABLE ( Previous ) ), "Added",
    MAX ( 'Current'[Price] ) < 50, "Dropped - Due to Price",
    MAX ( 'Current'[Quantity Sold] ) < 60, "Dropped - Due to Quantity Sold",
    ""
)

CNENFRNL's avatar
CNENFRNL
Community Champion
5 years ago

Hi, matsahiro , simple enough, Current table is like this,

ProductPriceQuantity Sold

Hat 55 70
Shirt 40 70
Shoes 60 60
Short 70 60
Pants 100 10

 

matsahiro's avatar
matsahiro
Helper II
5 years ago

CNENFRNL Okay, great. Last question. I understand the data and how you created the add/dropped column. But, if we are creating a table visual where the visual level filters are set to price>50 and quantity sold>60, how do I bring in the products into the view that used to meet this requirement the last period but no longer do now? (referring to displaying the "dropped" products in the view)