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
Anonymous
Not applicable

Remove filter with measure

How to filter a date and use a measure remove that previous filter?

For example: If I filter January show me all itens in my table that have a January date, but if I filter February I need to see all itens from January and February, if select March see all itens from January to March.

 

I tried this code but didn't have success.

 

SWITCH(TRUE(),

SELECTEDVALUE(MyTable[PlannedMonth])="January", CALCULATE(COUNTA(MyTable[PlannedMonth]), ALL(MyTable[PlannedMonth]), MyTable[End Date]<=DATEVALUE("31/01/2022")),

SELECTEDVALUE(MyTable[PlannedMonth])="February", CALCULATE(COUNTA(MyTable[PlannedMonth]), ALL(MyTable[PlannedMonth]), MyTable[End Date]<=DATEVALUE("28/02/2022"))

 

Where the column "PlannedMonth" are the month name and "End Date" are my numerical dates

1 ACCEPTED SOLUTION
v-yanjiang-msft
Community Support
Community Support

Hi @Anonymous ,

According to your description, I create a sample.

vkalyjmsft_0-1655189485314.png

If you want to count the number of days less than or equal to the selected month, in my sample your formula works.

vkalyjmsft_1-1655189740800.png

If you want to display all dates from January and February when you filter February, here's my solution.

1. Create a new month table, don't make relationship between the two tables.

vkalyjmsft_2-1655190110125.png

2.Create a measure.

Check =
IF (
    MAX ( 'MyTable'[End Date] )
        <= MAXX (
            FILTER (
                ALL ( 'MyTable' ),
                FORMAT ( 'MyTable'[End Date], "MMMM" ) = SELECTEDVALUE ( 'Month'[Month] )
            ),
            'MyTable'[End Date]
        ),
    1
)

Put the new month column in a slicer and put the measure in the visual filter and select its value to 1, get the result.

vkalyjmsft_3-1655190505791.png

I attach my sample below for reference.

 

Best Regards,
Community Support Team _ kalyj

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

 

View solution in original post

2 REPLIES 2
v-yanjiang-msft
Community Support
Community Support

Hi @Anonymous ,

According to your description, I create a sample.

vkalyjmsft_0-1655189485314.png

If you want to count the number of days less than or equal to the selected month, in my sample your formula works.

vkalyjmsft_1-1655189740800.png

If you want to display all dates from January and February when you filter February, here's my solution.

1. Create a new month table, don't make relationship between the two tables.

vkalyjmsft_2-1655190110125.png

2.Create a measure.

Check =
IF (
    MAX ( 'MyTable'[End Date] )
        <= MAXX (
            FILTER (
                ALL ( 'MyTable' ),
                FORMAT ( 'MyTable'[End Date], "MMMM" ) = SELECTEDVALUE ( 'Month'[Month] )
            ),
            'MyTable'[End Date]
        ),
    1
)

Put the new month column in a slicer and put the measure in the visual filter and select its value to 1, get the result.

vkalyjmsft_3-1655190505791.png

I attach my sample below for reference.

 

Best Regards,
Community Support Team _ kalyj

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

 

SpartaBI
Community Champion
Community Champion

@Anonymous follow this patten and you'll solve your question and much more 🙂
https://www.daxpatterns.com/standard-time-related-calculations/



2022-05-19 17_30_22-Re_ Need help on DAX function with measure vs colu... - Microsoft Power BI Commu.png

Showcase Report – Contoso By SpartaBI


SpartaBI_3-1652115470761.png   SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Full-Logo11.png

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.