Filter/slicer problems with calculated columns?

[401evolr]

I'm trying to apply filters or slicers to my data in the report view without luck. Might have something to do with calculated columns?

I have one table that is a list of work for the year (2266 jobs total across all 'programs' and 'areas'):

I created a second table (with all calculated columns) to calculate how many jobs need to be done each day such that they're all complete by end of year. Then you can see how many jobs should be remaining on each calendar day. This is shown by the target 'Burndown' column:

In the report view, this target burndown line plots nicely (showing 2266 jobs on Jan 1 and 0 on Dec 31). However, I'm trying to add filters or a splicer so that I can see a burndown line only for jobs with a certain "program" or "area" attribute. As you can see, I've set up a filter to show only "south" areas and "instrument" programs - yet the burndown line remains unchanged.

Is there a reason the filters aren't applying to my calculated columns?

[amitchandak]

@401evolr , is this new table joined with your dimension table, so that filter can apply

[401evolr]

Yes, here are the relationships I have set up:

[401evolr]

Thanks for the replies @nandukrishnavs and @amitchandak 

Changing the relationship to "bi-directional", I'm able to filter it somewhat, but it's incorrect. It changes the date along the x-axis, whereas I'm expecting it to change the number of jobs shown along the Y-axis:

[mahoneypat]

Keep your relationship going one way (the original way you had it), and try a measure like this:

NewMeasure = Calculate(AVERAGE(GovtExchangesBurndown[Burndown]), GovtExchangesAll)

Since you are using the many table in the calculate, it will honor the slicer affecting that table.  This is one way to make filters flow "uphill".

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[401evolr]

Thanks @mahoneypat 

The new measure gives me the same result as before unfortunately. The filters are having an effect on the dates, but not the intended fields:

[Icey]

Hi @401evolr ,

Please create a dummy pbix file for test. Please remove sensitive information and replace real data with dummy data. It is suggested to upload your file to OneDrive for Business and then paste the link here.

Best Regards,

Icey

[401evolr]

Thanks @Icey 
Here is the link to the pbix file. Let me know if it doesn't work.

[Icey]

Hi @401evolr ,

Sorry to reply late. 

First, filters or slicers can't dynamically change the result of calculated column / table.

Then, try to create a measure like so:

Measure =
VAR AnnualCount_ =
    CALCULATE (
        COUNT ( GovtExchangesAll[Status] ),
        ALLSELECTED ( GovtExchangesAll )
    )
VAR DailyTarget_ = AnnualCount_ / 365
VAR YTDTarget_ =
    CALCULATE ( SUM ( GovtExchangesBD[DailyTarget] ), DATESYTD ( DimDate[Date] ) )
VAR Burndown_ = AnnualCount_ - YTDTarget_
RETURN
    AVERAGEX ( GovtExchangesBD, Burndown_ )

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[401evolr]

@Icey thanks for your reply. It looks like this measure will indeed allow for dynamic filtering. However, it's calculating it incorrectly. Notice that the year-end numbers are negative when the filtering is applied - but they should always be 0.

I think what's happening is that the function to calculate the Daily Target is not being filtered. For example, there are 1000 total items so the daily burndown is 1000/365 = 2.7. However, if we filter the type to just "instruments", there are 142 total items so the daily burndown should be 142/365 = 0.4. But, it keeps applying the 2.7 instead.

Is there a way to have the Daily Target function be dynamic as well?

[401evolr]

@mahoneypat thanks for the reply. I don't quite follow - where would i use this new measure?

[mahoneypat]

Your first post showed the average of burndown in the Line Values area of your chart.  You can try this measure there.

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[nandukrishnavs]

@401evolr 

You have to enable the relationship between these two tables.

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂