Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
rsch91
Helper I
Helper I

Ignore date filter but not everywhere

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")
3 REPLIES 3
johnt75
Super User
Super User

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.

bhanu_gautam
Super User
Super User

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




Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!




LinkedIn






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. 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.