Forum Discussion
Formula help
Hi mazwro - CALCULATE does not directly process OR logic as expected when used inside its filter arguments. Instead, it evaluates each condition independently, which can lead to unexpected results like blank values.
Count_Open_Problems =
VAR SelectedStartDate = MIN(dim_date[Date]) -- Start date from slicer
VAR SelectedEndDate = MAX(dim_date[Date]) -- End date from slicer
RETURN
CALCULATE(
DISTINCTCOUNT(vw_PM_Dashboard[IDINC]),
FILTER(
vw_PM_Dashboard,
vw_PM_Dashboard[Date_Created] < SelectedStartDate && -- Created before the selected period
(
vw_PM_Dashboard[Date_Closed] > SelectedEndDate || -- Closed after the selected period
ISBLANK(vw_PM_Dashboard[Date_Closed]) -- Or still open
)
),
REMOVEFILTERS(dim_date[Date]) -- Ignore slicer filters on the date dimension
)
Cross-check the results in SQL and Power BI to ensure the data matches as expected.
Hope this helps.
hello rajendraongole1 thank you for the help. I am not sure if this is an error though.
For throubleshoot, I tried with a simpler query, where for slicer Month I get counts of incidents opened before the start date and closed after the end date and this also gives me blanks and I do not understand why. Can you help?
VAR SelectedStartDate = MIN(dim_date[Date]) -- Start date from slicer
VAR SelectedEndDate = MAX(dim_date[Date]) -- End date from slicer
RETURN
CALCULATE(
DISTINCTCOUNT(vw_PM_Dashboard[IDINC]),
vw_PM_Dashboard,
vw_PM_Dashboard[Date Created] < SelectedStartDate && -- Created before the selected period
vw_PM_Dashboard[Date Closed] > SelectedEndDate-- Closed after the selected period
,
REMOVEFILTERS(dim_date[Date]) -- Ignore slicer filters on the date dimension
)