I'm working on a report that sources pre-aggregated data. I'll use simple example data as I cannot share our data.
In terms of the example, we cannot relate [Beverages Consumed] to the [Vehicles Owned], so there's no way to know which car owners consumed what type of beverages. We also can't relate [Vehicle Type] to [Population], except within year. That's why this data is on different rows entirely.
|Year||Population||Vehicle Type||Vehicles Owned||Beverage Type||Beverages Consumed|
Our problem is with the filter:
Using the Vehicle Type filter, we can remove vehicle types from the calculation to omit them, but since we have NULLS for the population rows, Null shows up in the filter selection. If we were to remove the nulls, the calculation cant compute because population values have been removed with the NULLs.
I've tried joining the different data on year, but then we get duplicate counts when summing the measures.
If we could keep nulls, but not give them as an option on the filter, that would be great. But, anyway I have tried to remove NULLs from the filter options, excludes the NULLs from the calculation.
Is there a way to leave the rows needed for the calculation, but keep it from showing up in the filter?
If [Filter Values] represent the filter options, the SQL equivalent would be: [Vehicle Type] = 'NULL' OR [Vehicle Type] IN([Filter Values])
LOD Problem.twbx 28.8 KB