Forum Discussion
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 requirement, but also show products that were recently dropped from that previous list from last quarter for not making a certain requirement now. Any ideas on writing the DAX to make this "dropped/added" column?
Date 1/1/2021, Products with price>50 and quantity sold>60
Previous Report
| Product | Price | Quantity Sold |
| Shoes | 60 | 60 |
| Shirt | 60 | 70 |
| Short | 70 | 60 |
| Pants | 100 | 80 |
Date 3/31/2021, Products with price>50 and quantity sold>60, includes products that were dropped from Previous Report -
New View with Dropped/Added Indicator
| Product | Price | Quantity Sold | Dropped/Added |
| Hat | 55 | 70 | Added |
| Shirt | 40 | 70 | Dropped - Due to Price |
| Shoes | 60 | 60 | |
| Short | 70 | 60 | |
| Pants | 100 | 10 | Dropped - Due to Quantity Sold |
How do I create a view of these top performing products by those filters but also include "dropped" products that do not meet these requirements anymore?
Thank you
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", "" )
5 Replies
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 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
