Ignore date filter but not everywhere

[rsch91]

Hi all,

I have a measure for the number of appointments, distinct by date. I want to plot this in a table by opdracht_ID with a date (year) slicer (from table Dim_Datum/Dim_Date). Any opdracht_ID (Job ID) has multiple reference dates and the Dim_Datum (Dim_Date) table filters by reference date. Let's say an opdracht_ID has appointments in december 2024 and january 2025. In that case the opdracht_ID also has reference dates in both 2024 and 2025. Without the ALL(Dim_Datum) command, it only shows the appointments in 2024 when the table is filtered in 2024, and the number of appointments in 2025 when filtered in 2025. But, with the ALL() command it shows literally ALL opdracht_IDs, so also the ones from 2022. 

When my table is filtered in 2025, I want to show all opdracht_IDs with a reference date in 2025, but also count any appointments (and therefore reference dates) in 2024 should the opdracht_ID have it. How do I do that in DAX? Note: a calculated table is not an option as my company works with semantic models and automated refreshes don't work with calculated tables. 

This is the line of DAX I currently have: 

CALCULATE(

    COUNTROWS(

        DISTINCT(

            SELECTCOLUMNS(

                Feit_Onderhoudsopdracht_communicatie,

                Feit_Onderhoudsopdracht_communicatie[Opdracht_ID],

                "Datum afspraak",

                    FORMAT(Feit_Onderhoudsopdracht_communicatie[Gewijzigde_datum_en_tijd], "DD/MM/YYYY")))),

ALL(Dim_Datum),

Feit_Onderhoudsopdracht_communicatie[Status] = "Afspraak huurder",

Dim_Onderhoudsopdracht[Status] = "Teruggemeld" || Dim_Onderhoudsopdracht[Status]= "Afgehandeld")

[johnt75]

You can try

Num Jobs =
VAR VisibleJobs =
    CALCULATETABLE (
        DISTINCT ( Feit_Onderhoudsopdracht_communicatie[Opdracht_ID] ),
        Feit_Onderhoudsopdracht_communicatie[Status] = "Afspraak huurder",
        Dim_Onderhoudsopdracht[Status] = "Teruggemeld"
            || Dim_Onderhoudsopdracht[Status] = "Afgehandeld"
    )
VAR Result =
    CALCULATE (
        COUNTROWS (
            DISTINCT (
                SELECTCOLUMNS (
                    Feit_Onderhoudsopdracht_communicatie,
                    Feit_Onderhoudsopdracht_communicatie[Opdracht_ID],
                    "Datum afspraak",
                        FORMAT (
                            Feit_Onderhoudsopdracht_communicatie[Gewijzigde_datum_en_tijd],
                            "DD/MM/YYYY"
                        )
                )
            )
        ),
        ALL ( Dim_Datum ),
        Feit_Onderhoudsopdracht_communicatie[Status] = "Afspraak huurder",
        Dim_Onderhoudsopdracht[Status] = "Teruggemeld"
            || Dim_Onderhoudsopdracht[Status] = "Afgehandeld",
        VisibleJobs
    )
RETURN
    Result

This first generates a list of jobs which are visible in the current filter context. Then it uses these jobs as a filter, ignoring any date filters.

[rsch91]

Thank you. This worked. I noticed the other day that there's more problems with the filter context. I have a measure for a certain date for instance and it has the same problem as the number of appointment measures. How would this work for the following piece of DAX?

CALCULATE(

                            DATE(

                              YEAR(MIN(Feit_Onderhoudsopdracht_communicatie[Datum en tijd])),

                                MONTH(MIN(Feit_Onderhoudsopdracht_communicatie[Datum en tijd])),

                             DAY(MIN(Feit_Onderhoudsopdracht_communicatie[Datum en tijd]))),

                            FILTER(Feit_Onderhoudsopdracht_communicatie, Feit_Onderhoudsopdracht_communicatie[Status] = "Bewoner belt terug"))

[johnt75]

What exactly are you trying to achieve ?

[rsch91]

A correct report. I have a table with a number of variables per opdracht_ID (job_ID). For instance the date of the first appointment. Same issue with the number of appointments, when table is filtered for 2025, it shows the first appointment in 2025. But the actual first appointment could have been in 2024. And I am interested in the actual first apointment.  

Thank you. 

[johnt75]

The basic pattern is the same. First create a variable containing the IDs of all the jobs which fit the current filter context, and any other filters you need. Then pass that variable as a filter to CALCULATE whatever measure you need, removing the filters on the date table.

e.g.

Earliest job =
VAR VisibleJobs =
    CALCULATETABLE (
        DISTINCT ( Feit_Onderhoudsopdracht_communicatie[Opdracht_ID] ),
        Feit_Onderhoudsopdracht_communicatie[Status] = "Afspraak huurder",
        Dim_Onderhoudsopdracht[Status] = "Bewoner belt terug"
    )
VAR Result =
    CALCULATE (
        MIN ( Feit_Onderhoudsopdracht_communicatie[Datum en tijd] ),
        REMOVEFILTERS ( Dim_Datum ),
        Feit_Onderhoudsopdracht_communicatie[Status] = "Bewoner belt terug",
        VisibleJobs
    )
RETURN
    Result

[rsch91]

Thanks. So I tried your piece of DAX but it's not giving me the result I want. Still only shows dates in 2025. I have rewritten the DAX as follows, but it has the following error: The True/False expression does not specify a column. Each True/False expressions used as a table filter expression must refer to exactly one column. Where did I go wrong? 

VAR VisibleDATE =

    CALCULATE(

       MIN(Feit_Onderhoudsopdracht_communicatie[Datum en tijd]),

        FILTER(Feit_Onderhoudsopdracht_communicatie, Feit_Onderhoudsopdracht_communicatie[Status] = "Afspraak huurder"))

VAR Result =

    CALCULATE (

        MIN(Feit_Onderhoudsopdracht_communicatie[Datum en tijd]),

        FILTER(Feit_Onderhoudsopdracht_communicatie, Feit_Onderhoudsopdracht_communicatie[Status] = "Afspraak huurder"),

        REMOVEFILTERS(Dim_Datum),

        VisibleDATE

    )

RETURN

    Result

[johnt75]

Your code is trying to calculate the same minimum date twice, rather than trying to calculate the minimum date across visible jobs.

When you place the filter on year 2025 is that filter being placed on the Dim_Datum table ? If not then you will need to amend the code I posted to also remove filters from that table.

[bhanu_gautam]

@rsch91 , Try using

DAX
CALCULATE(
COUNTROWS(
DISTINCT(
SELECTCOLUMNS(
Feit_Onderhoudsopdracht_communicatie,
Feit_Onderhoudsopdracht_communicatie[Opdracht_ID],
"Datum afspraak",
FORMAT(Feit_Onderhoudsopdracht_communicatie[Gewijzigde_datum_en_tijd], "DD/MM/YYYY")
)
)
),
REMOVEFILTERS(Dim_Datum),
KEEPFILTERS(
VALUES(Dim_Datum[Year])
),
Feit_Onderhoudsopdracht_communicatie[Status] = "Afspraak huurder",
Dim_Onderhoudsopdracht[Status] = "Teruggemeld" || Dim_Onderhoudsopdracht[Status] = "Afgehandeld"
)

[rsch91]

Thank you. Unfortunately it does not work. Still only shows the number of appointments in 2025 when filtered for 2025, while there's appointments in 2024 as well.