topic filter does not work when columns are not selected in DAX Commands and Tips

filter does not work when columns are not selected

rjobaan — Tue, 08 Oct 2024 06:44:19 GMT

I have the following filter created

filter_dates_general =
VAR result =
IF(

SELECTEDVALUE(fct_revenue[Start_Date])>= MIN(dim_calendar[Date])
&&SELECTEDVALUE(fct_revenue[Start_Date])<=MAX(dim_calendar[Date])
||
SELECTEDVALUE(fct_revenue[End_Date])>= MIN(dim_calendar[Date])
&&SELECTEDVALUE(fct_revenue[End_Date])<=MAX(dim_calendar[Date])
||
SELECTEDVALUE(fct_revenue[Start_Date])<= MIN(dim_calendar[Date])
&&SELECTEDVALUE(fct_revenue[End_Date])>=MAX(dim_calendar[Date]),

1,
0)

RETURN result

and a between slicer based on date.

i removed the relationship between fct_revenue and dim_calendar for this

when i have a table with region, absence_code, start_date, end_date, and above filter =1

this filter works fine and is showing all regions which fullfil above formula. so within the date range. or even when start and end date are the same its working.

But if i create a piechart based on only absence_code its not working anymore because start and end _date are not within the context anymore..

So what to do are to adjust that above filter is working for every dimension within dashboard?

Re: filter does not work when columns are not selected

Kedar_Pande — Tue, 08 Oct 2024 06:52:34 GMT

When you use a pie chart with just the absence_code, the Start_Date and End_Date fields lose context because they are not included in the visual.

Adjusted Measure:

filter_dates_general =
VAR minDate = MIN(dim_calendar[Date])
VAR maxDate = MAX(dim_calendar[Date])

RETURN
CALCULATE (
IF (
MAX(fct_revenue[Start_Date]) >= minDate &&
MAX(fct_revenue[Start_Date]) <= maxDate ||
MAX(fct_revenue[End_Date]) >= minDate &&
MAX(fct_revenue[End_Date]) <= maxDate ||
MAX(fct_revenue[Start_Date]) <= minDate &&
MAX(fct_revenue[End_Date]) >= maxDate,
1,
0
),
ALLSELECTED(fct_revenue)
)

You can use this adjusted measure in a visual filter to filter the pie chart, ensuring that it respects the date range and the absence code context.

Re: filter does not work when columns are not selected

rjobaan — Tue, 08 Oct 2024 07:15:18 GMT

but now it doesnt taken into account the values of the slicer right?

when i used above filter on the pie-chart with filter=1 its showing nothing

Re: filter does not work when columns are not selected

Anonymous — Wed, 09 Oct 2024 09:04:33 GMT

Thanks for the reply from Kedar_Pande , please allow me to provide another insight:

Hi, rjobaan

Based on my testing, I recommend modifying your measure as follows:

filter_dates_general = VAR MinDate = MIN(dim_calendar[Date]) VAR MaxDate = MAX(dim_calendar[Date]) VAR result = IF( (MAX(fct_revenue[Start_Date]) >= MinDate && MAX(fct_revenue[Start_Date]) <= MaxDate) || (MAX(fct_revenue[End_Date]) >= MinDate && MAX(fct_revenue[End_Date]) <= MaxDate) || (MAX(fct_revenue[Start_Date]) <= MinDate && MAX(fct_revenue[End_Date]) >= MaxDate), 1, 0 ) RETURN result

Although in DAX, the && (AND) operator has a higher precedence than the || (OR) operator, it is advisable to ensure each logical condition is correctly enclosed in parentheses for accuracy and readability. This helps avoid any issues with logical operation precedence.

Here is my test example:

Test results are as follows:

Before using the measure as a filters:

After using the measure as a filters:

Currently, no issues have appeared in my test results.

Can you share sample data and sample output in tabular format if I am misunderstanding? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

For questions about uploading data, you can try the following links:

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Solved: How to upload PBI in Community - Microsoft Fabric Community

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: filter does not work when columns are not selected

rjobaan — Mon, 14 Oct 2024 06:08:42 GMT

that is not working. finally i find the correct code:

filter_dates_general =
VAR SelectedStartDate = MIN(dim_calendar[Date])
VAR SelectedEndDate = MAX(dim_calendar[Date])
RETURN
CALCULATE(
DISTINCTCOUNT(fct_revenu[DIM_Customer_ID]),
KEEPFILTERS(
(fct_revenu[Start_Date] >= SelectedStartDate && fct_revenu[Start_Date] <= SelectedEndDate) ||
(fct_revenu[End_Date] >= SelectedStartDate && fct_revenu[End_Date] <= SelectedEndDate) ||
(fct_revenu[Start_Date] <= SelectedStartDate && fct_revenu[End_Date] >= SelectedEndDate)
)
)