Forum Discussion
Measure on table visual destroys filters
I found the solution: it seems that row inclusion in a table works differently if there are measures in the table visual or not.
If there are no measures in the table visual then rows are included if the data satisfies the current filters.
If there are measures in the table visual then rows are included if any of the measures is not blank, ignoring filters on the table columns themselves.So, solution to the original question is to change the measure as follows:
StartReportingDate = IF(ISBLANK(MIN(Data[Value1])),BLANK(), MIN(Dates[Date]))
Where we know Value1 is never blank in the original data.As for the example I've uploaded, the solution is:
First issue: ensure the measures are BLANK if outside of date range.
So in the example:
Remove IsInDateRange from the table visual and change ReportingStartDate measure as follows:
ReportingStartDate = IF([IsInDateRange],MIN('Date'[Date]),BLANK())Second issue: change RealReportStartingDate to use ReportStartingDate instead of MIN(Date)
RealReportStartingDate was defined as a column, but should have been a measure. So redefine it as a measure:
RealReportingStartDate = IF([IsInDateRange],MAX([ReportingStartDate],MIN(MachineContractInfo[StartDate])),BLANK())Third issue: ensure the measures are BLANK if outside of filter range.
This is solved by the adjustments above. The IsInDateRange column was causing the problem since it never returned BLANK(). By removing it from the visual the problem is solved since the other measures are blank outside of filter range.
I've uploaded an example at the following link: https://computerscuypers-my.sharepoint.com/:u:/g/personal/stefan_comcu_eu/EfZdQ_LTn7BOnAkIcAfSzxoBbszHokv2-Jow3b4WNpffAA?e=oio43s
I can't really use the real report. It has confidential data in it and is also quite big (the pbix file has 64MB).
The base table I'm working on has information about rental contracts for machines. It has the following columns:
- ContractNo : identifies the contract. One contract can have multiple machines.
- ClientNo : the client
- MachineNo : the machine
- StartDate : the date the machine started within the contract
- EndDate : the date the machine ended within the contract
The customer has asked for a report on all the contracts between a start- and end date (e.g. from Jan 1 2021 to Dec 31 2021, but it does not necesserily have to be a whole year).
Ths customer also wants a column with the real reporting start date. That is the start date of the report or the start date of the contract whichever is bigger.
And based on that start date he also wants a mileage for the machine on that real date (this uses interpolation into a mileage table, which is not included in this sample report).
Similar for the end dates.
I've come up with a number of issues while developping this report.
First issue: I would like to filter the contract table by date. I tried to do this by creating the IsInDateRange measure on which I hoped to filter. But that does not seem to work (I can add IsInDateRange to the filters of the visual, but I can't select a value to filter on).
Second issue: I defined the RealReportingStartDate as MAX(MIN('Date'[Date]),MachineContractInfo[StartDate]) which I would expect to give me MAX(2021-01-01,2020-01-05) = 2021-01-01 on the first row but it shows 2020-01-05.
Third issue: On the report I've put 2 tables, one without any of the measures and one with the measures added. I've filtered on 'Client 1'. The table with the measures added seems to ignore the filter on 'Client 1' and shows all the records. Not clear to me why.