Forum Discussion
How to create single select slice but using multiple conditions
We are converting a Jasper Report to Power BI. In the Jasper report, user can select "Customer Type" filter that has "Active" and "All" values. Based on the selected filter, the data will be displayed. However, the data displayed by "Active" or "All" depends on another date range filter.
Following is part of the actual proceedure we are using. In following f is a table called "customer" and "f.startdate" & "f.enddate" are two columns in the table. "P_startDateParam" & "P_endDateParam" are param set by another date range filter in the same report.
CASE
WHEN P_statusTypeParam = "active" THEN (f.enddate >= P_endDateParam OR f.enddate IS NULL) AND (f.startdate < P_endDateParam)
WHEN P_statusTypeParam = "all" THEN ((f.startdate BETWEEN P_startDateParam AND P_endDateParam)
OR (f.enddate BETWEEN P_startDateParam AND P_endDateParam)
OR (f.startdate < P_startDateParam AND (f.enddate IS NULL OR enddate > P_endDateParam)))
The viewer of the report should be able to select the date range for the report as well as "Active" or "All" "Customer Type" in the report. So far I have found Field Parameters that works only in the View, and Parameters in Power Query, which works in
3 Replies
Power BI does not support OR filters across columns. You would need to write a measure that implements the desired logic and then use that measure as a visual filter.
I'm guessing I'm in the right path. However, I have an issue testing if the enddate in the current row is blank or not.
Measure =
VAR StartDate = FIRSTDATE(CustomersPlan[startdate])
VAR EndDate = LASTDATE(CustomersPlan[enddate])
RETURN
IF(CustomersPlan[enddate] = BLANK(), "Active", "All")
^^^^^^^^^^^^^^^^^^^^^A single value for column 'enddate' in table 'CustomersPlan' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.Do you have any idea how to refer the current row value of a date type in a measure?
Do you have any idea how to refer the current row value of a date type in a measure?Great question. This is the foundation of DAX, the difference between row context and filter context.
Use an aggregate as suggested by the message.
