Measure on table visual destroys filters

[StefanC]

I have an issue in PowerBI with putting a measure on a table visual that I don't understand.
I'm trying to add a measure that uses MIN(date) and it seems to destroy the filter context.
I've reproduced the issue in a simple report as follows:
Create 2 tables:
Dates = CALENDAR(dt"2021-01-01",dt"2021-02-01")
Data =
{
(DATE(2021, 1, 1), "A"),
(DATE(2021, 1, 2), "B"),
(DATE(2021, 1, 3), "C"),
(DATE(2021, 1, 3), "D")
}
Then define relationship between Dates[Date] and Data[Value1].
Add a measure to the Dates table:
StartReportingDate = MIN(Dates[Date])
Next add a Table visual to the report showing Data[Value1] and Data[Value2].
Next set a filter on Dates[Date] = 1/1/2021
The table correctly shows only one record (with Value2 = 'A')
Now add the StartReportingDate measure to the table.
The table now shows all Data records and it seems the filter context is not working any more.

Any idea how I can solve this?

[StefanC · Accepted solution]

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.

[StefanC]

I've uploaded an example at the following link: https://computerscuypers-my.sharepoint.com/:u:/g/personal/stefan_comcu_eu/EfZdQ_LTn7BOnAkIcAfSzxoBbs...
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.

[amitchandak]

@StefanC , use min of

StartReportingDate = MIN(Data[Value1])

Min of date on dimension is applicable for rows of table

StartReportingDate = MIN(Dates[Date])

To force filter on the value table you can try

StartReportingDate = MINX(Data, RELATED(Dates[Date]))

[StefanC]

Thank you for the quick reply, but it isn't exactly what I need. I really want the reporting period. In the actual report I need to make some calculations per row that require the start- and end date of the reporting. I there have data rows with a start date and end date and need to calculate the usage percentage during the period ((end date - start date) / (reporting end date - reporting start date)).

If I extend the date tabel in the sample by starting in 2020 instead of 2021 and change the date filter to 'before Jan 3 2021' the results are no longer what I need.

The original StartReportingDate shown on a card then shows 1/1/2020 which is correct.

The formula's you propose both show Jan 1 2021 on row 'A' and Jan 2 2021 on row B. There I would expecte 1/1/2020 on all rows.

[foodd]

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.