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

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",
    ""
)

  • matsahiro's avatar
    matsahiro
    Helper II
    5 years ago

    CNENFRNL  This helps out greatly. Thank you very much for your help!

  • matsahiro's avatar
    matsahiro
    Helper II
    5 years ago

    CNENFRNL Unfortunately it seems I can't open the PBIX, probably due to me having a different desktop version. It is possible to attach screenshots of the data you entered in for "Current"? I'm curious as to whether you only included products that met all the requirements or not in this "current" data table. Basically im trying to figure out how you populated the table view with the dropped products from the previous table if the filters overall are set to price>50 and quantity sold>60, considering how the dropped products no longer meet the requirements to be in the view

    • 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)