Forum Discussion

Grayfox88's avatar
Grayfox88
New Member
6 years ago
Solved

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 s...
Tips and Tricks
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    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)

     

     

     

     


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


    Best regards,

     

Grayfox88's avatar
Grayfox88
New Member
6 years ago

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]))

 

 

 

v-lid-msft's avatar
v-lid-msft
Community Support
6 years ago

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,