Forum Discussion

rjobaan's avatar
rjobaan
Frequent Visitor
1 year ago
Solved

filter does not work when columns are not selected

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?

 

 

 

  • rjobaan's avatar
    rjobaan
    1 year ago

    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)
    )
    )

4 Replies

  • rjobaan's avatar
    rjobaan
    Frequent Visitor
    1 year ago

    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)
    )
    )

  • Kedar_Pande's avatar
    Kedar_Pande
    Super User
    1 year ago

    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.

    • rjobaan's avatar
      rjobaan
      Frequent Visitor
      1 year ago

      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

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        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.