Dealing with blanks when calculating a running total count with filters

[paulmorgan]

Hi

I have three tables:

1) Events Table ('tblGlobal'): list of events with associated years and other metadata (for this example, using one of them - "Terrain")

2) Terrains Table ('tblTerrains'): Related table listing all "Terrains"

3) Dates Table ('tblDates'): Related Table listing all "Years"

I need a stacked bar chart listing events, with the legend being Terrain.

I calculated a running total count of events by year and learned how to avoid blanks using the following:

Measure =

CALCULATE (

COUNTA('tblGlobal'[Country]),

FILTER ( ALLSELECTED( 'tblGlobal' ),

'tblGlobal'[End year] <= max(tblDates[Years]) && 'tblGlobal'[Terrain]<>"Offshore"

)

)

I created two calculated measures: one for <>"Offshore" and one for ="Offshore". That was the only way I could figure out how to de facto apply a legend (in effect) because when I left off the additional filter item in the formula and just used Terrain as a legend, the chart lost the desired effect of removing blanks. If you have ideas on what I could have done differently there, let me know. But that is not the main issue.

(Added after comment below) Also, I have "Start Year" and "End Year" as columns. I have to take the net number. So each of Onshore and Offshore have three separate measures. One for Start Year, one for End Year, and one for "Net". 

The approach works great as long as I don't filter on Terrain or any other metadata in the first table. In other words, if for example, I only want the chart to show Offshore, it will show the right cumulative numbers in the right years in which the events occur, but the blanks show up again. And it doesn't show the full axis of years (even though I checked "show items with no data"). Or if I filter on one of the other columns (e.g. "Status" is another one), again, the correct cumulative figures show up in the right years, but the blanks show up and not all years.

Some screenshots below (the first one is the only one that is correct; the other two are what show up when filters are applied). I appreciate any ideas people may have to educate me on how I could do this better.

Cheers

[amitchandak]

@paulmorgan , Try like

Measure =
CALCULATE (
COUNTA('tblGlobal'[Country]),
FILTER ( ALLSELECTED( 'tblDates' ),
'tblDates'[End year] <= max(tblDates[Years])),FILTER ( 'tblGlobal'[,'tblGlobal'[Terrain]<>"Offshore"
)
)

assuming tblDates is a date table

[paulmorgan]

Thanks for the thought.

I could try that, but my challenge is that I need to deal with events that start and events that end. So I actually created three measures for each terrain. One as above using Start Year, one using End Year and one that subtracts one from the other. So I'm not clear how to apply that distinction in the proposed approach.

Sorry for the additional limitation. I should have provided a complete description of the challenge.