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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. 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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.

Top Solution Authors