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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
matsahiro
Helper II
Helper II

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

ProductPriceQuantity Sold
Shoes6060
Shirt6070
Short7060
Pants10080

 

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

ProductPriceQuantity SoldDropped/Added
Hat5570Added
Shirt4070Dropped - Due to Price
Shoes6060 
Short7060 
Pants10010Dropped - 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

1 ACCEPTED SOLUTION
CNENFRNL
Community Champion
Community Champion

@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,

Screenshot 2021-02-15 231923.png

 

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

Screenshot 2021-02-15 233146.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

View solution in original post

5 REPLIES 5
CNENFRNL
Community Champion
Community Champion

@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,

Screenshot 2021-02-15 231923.png

 

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

Screenshot 2021-02-15 233146.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

@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

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

 


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

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

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.