DAX help for filtering Cumulative and Static Value

DAX DAX Calculations
1

Hi Team,

Hope you are doing well.

I have created a cumulative measure (Cumulative Actual Hours) to show hours spent on a piece of work over time (Date Entered). Each work has fixed budget (Budget Hours) and I’m using a line chart to show the total budget and whether the time entries are nearing budget or have exceeded it.

I want Budget hours to be shown as a straight line and Cumulative Actual Hours as an increasing line approaching it. (Fig 1).

The Problem: When I filter by Work Name the Cumulative Actual Hours is within certain Date Entered but Budget Hours shows the entire range of Date Entered . (Fig 2) I need the Budget hours line to only extend within the Date Entered in the particular work.

I can achieve this if I create use Both Cross Filter Direction between time and work table (Fig 3). But this will break the chart when there is no Work selected as Budget Hours is now filtered by time and is no longer a straight line (Fig 4).

I have attached sample pbix file.

Is there any supporting measure I can write or any dax changes that I can do to Budget hours to achieve this?

Appreciate your help :slight_smile:

Many Thanks
SuraajWork Sample.pbix (24.7 KB)

Fig 4
Fig 41581×706 76.2 KB
Fig 3
Fig 31596×767 58.6 KB
Fig 2
Fig 21574×700 69.3 KB
Fig 1
Fig 11599×668 78.3 KB

2

Hi @Suraaj,

Just add a test to see if one or multiple work id’s are selected. Give this a go.

Budget Hours v2 = 
IF( COUNTROWS( VALUES( 'Work'[Work Name] )) =1,
    CALCULATE( [Budget Hours], 
        FILTER( 'Time',
            NOT( ISBLANK( [Cumulative Actual Hours] ))
        )
    ),
    CALCULATE( [Budget Hours], 
        FILTER( ALL('Time'),
            NOT( ISBLANK( [Cumulative Actual Hours] )) &&
            'Time'[Work ID] IN VALUES( 'Work'[Work ID] )
        )
    )
)

BTW you are missing a Date dimension table in the model…

I hope this is helpful.

1 Like
3

Hi @Suraaj I hope that you are having a great experience using the Support Forum so far. We’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!

4

Hi @Melissa ,

Thank you for the clever DAX solution :smile:

I had a few questions if that’s okay:

If there are Work Name with budget that has no time entered against it; looks like Budget Hours v2 skips through those as the work ID is not present in time table yet. Is there a way to accommodate this?

Also, I have added a date table and created relationship with Time table. This breaks my cumulative Actual hours measure even though I’m using entered date from time able. Do you know why its doing that?

Pbix attached.Work Sample.pbix (101.7 KB)

Many Thanks,
Suraaj

5

Hi @Suraaj,

Next you’ll need to start leveraging that Date dimension table in your visuals and measures.

Cumulative Actual Hours v2 = 
IF( NOT( ISBLANK( [Hours Entered] )),
    CALCULATE ([Hours Entered], 
        FILTER( ALLSELECTED('Date'[Date]),
            'Date'[Date] <= Max('Date'[Date])
        )
    )
)

I’ve placed the Date[Date] on the Axis in your Line Chart
A simple way to control visible dates is by creating a measure that you can use as visual level filter.

VisibleDateFilter = 
IF( NOT( ISBLANK( [Cumulative Actual Hours v2] )),
    1
)

.

Again you need to laverage the Date dimension and instead of ‘Time’[Work ID] IN VALUES( ‘Work’[Work ID] ) you can use ALLSELECTED( ‘Work’[Work ID] ) like below.

Budget Hours v3 = 
IF( COUNTROWS( VALUES( 'Work'[Work Name] )) =1 && NOT( ISBLANK( [Cumulative Actual Hours v2] )),
    CALCULATE( [Budget Hours], 
        FILTER( 'Date',
            NOT( ISBLANK( [Cumulative Actual Hours v2] ))
        )
    ),
    CALCULATE( [Budget Hours], 
        FILTER( ALL('Date'),
            NOT( ISBLANK( [Cumulative Actual Hours v2] ))
        ),
        ALLSELECTED( 'Work'[Work ID] )
    )
)

.

image
image1099×313 25.2 KB

Here’s your sample file. Work Sample v2.pbix (102.8 KB)
I hope this is helpful.

1 Like
6

@Melissa - Thank you so much for your help, the details, and explanations. This was really helpful :slight_smile: