Forum Discussion

StefanC's avatar
StefanC
Regular Visitor
3 years ago
Solved

Measure on table visual destroys filters

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 reprodu...
Need Help
  • StefanC's avatar
    StefanC
    3 years ago

    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.

     

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
3 years ago

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's avatar
StefanC
Regular Visitor
3 years ago

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.