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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Grayfox88
New Member

Filtering based on 2 values.

I am looking to filter my budget table based 2 values. Id like to filter each month as well as always display the “Monthly” items.  I’m not sure how best to go about this. I have tried disconnected slicers with DAX.

 

Title

Start Date

Budget Type

Amount

Travel

2/2/2020

Once

200

Office Supplies

1/1/2020

Monthly

500

Rental Equipment

1/1/2020

Monthly

100

Meals

1/1/2020

Monthly

200

Project 1

5/5/2020

Once

5000

Project 2

7/5/2020

Once

8000

1 ACCEPTED SOLUTION

Hi @Grayfox88 ,

 

We can create a measure and use it in visual filter to meet your requirement:

 

Measure = var t = FILTER(ALLSELECTED('Budget'), 'Budget'[Start Date].[Year] = YEAR(SELECTEDVALUE('Budget'[Start Date])) && 'Budget'[Start Date].[MonthNo] = MONTH(SELECTEDVALUE('Budget'[Start Date]) ))
return 
IF(COUNTROWS(FILTER(t,'Budget'[Budget Type]<>"Monthly"))+0 = 0 && COUNTROWS('Budget') <> 0  ,1,-1)

 

 11.jpg

 

 


If it doesn't meet your requirement, Please show the exact expected result based on the Tables that you have shared.


Best regards,

 

Community Support Team _ Dong Li
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

4 REPLIES 4
amitchandak
Super User
Super User

To handle dates better create a date table and join with your date.  And try like

MTD Sales = CALCULATE(SUM(budget[Amount]),DATESMTD('Date'[Date]),Budget[Budget Type]="Monthly")

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s.

Refer
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

 

Doing a join to dates for this scenario won’t work. As far as I can tell all filters are treated as an "and". Everytime you would filter on a month it will hide all other rows. For example, you select February and it only shows items with a start date of February, thus hiding all rows that are marked as monthly that have a different start date.

For a quick solution I have made a new calculated column that returns month name and if the row is a monthly item it will set it as *Monthly. This gives me a single point to filter on.

 

 

 

Budget Month = IF([Budget Type] = "Monthly","*Monthly",TEXT([Start Date],"mmmm")

 

 

This now creates a new problem where I can't display the total for the month for a monthly item calculated based on number of months shown

 

 

 

TotalPerMonth = IF (CONTAINS(Budget,Budget[Budget Type],"Monthly"),(SUM(Budget[Amount])*'Budget'[CountOfBudgetMonths]),SUM(Budget[Amount]))

 

 

 

Hi @Grayfox88 ,

 

How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?


Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @Grayfox88 ,

 

We can create a measure and use it in visual filter to meet your requirement:

 

Measure = var t = FILTER(ALLSELECTED('Budget'), 'Budget'[Start Date].[Year] = YEAR(SELECTEDVALUE('Budget'[Start Date])) && 'Budget'[Start Date].[MonthNo] = MONTH(SELECTEDVALUE('Budget'[Start Date]) ))
return 
IF(COUNTROWS(FILTER(t,'Budget'[Budget Type]<>"Monthly"))+0 = 0 && COUNTROWS('Budget') <> 0  ,1,-1)

 

 11.jpg

 

 


If it doesn't meet your requirement, Please show the exact expected result based on the Tables that you have shared.


Best regards,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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