Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I need to use DAX (not power query) to only display the whole record where there is a gap in revenue or the future revenues are blank. I've calculated the revenue and the revenue MoM but am struggling with how to display the entire record when a condition is met for only some of the records and exclude other records where the condition is not met at least once. Screen shots with my current DAX are below:
Measures:
Project Revenue
Project Revenue MoM = CALCULATE([Project Revenue],PREVIOUSMONTH(dim_Dates[Date]))
Project Revenue MoM Flag = IF((ISBLANK([Project Revenue])=TRUE() || [Project Revenue]=0) && [Project Revenue MoM]<>0,1)
I added conditional formatting to highlight the values where I would want to include the records. It's where the Project Revenue MoM Flag is equal to 1. As an example, for Code = P001932, I want to include the whole row's worth of records for all months. For Code = P001936, I don't want to see any of these records because the condition was not met for any of the records.
What is the correct DAX to display only the complete records where the condition is met at least once?
Hi @tk_moe
please try
Revenue New =
IF (
[Project Revenue MoM Flag] = 1,
CALCULATE ( [Project Revenue], ALLSELECTED ( 'Date' ) ),
[Revenue]
)
Thank you for your reply. I don't understand how this will help me filter the visual to only include codes with a gap in revenue or missing revenue. Would you please explain further?