Forum Discussion

gomezc73's avatar
gomezc73
Helper V
2 years ago
Solved

how filter the data based in 1 filter with multiple selection

Hi experts,

 

I have the following  slicer to select only 1 month, but now the users wants to select 1 or MORE months.

Currently in my measure i have a the SUMX with the following Filter:

Filter(Sales,Sales[Year_Month] = selectedvalue('Year_Month From'[Year_Month]).
 
It worked perfect with only 1 month selected. but when i select two or more months it doesn't work.
 
Can you please help me to understand how i must change that the filter to allow the SUMX work with several months selected?.. 
 
I really appreciate your help.

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi gomezc73 ,

     

    You may try the following expression

     

    Filter(Sales,Sales[Year_Month] in values('Year_Month From'[Year_Month])
     
    Filter(Sales,Sales[Year_Month] in values('Year_Month To'[Year_Month])
     

    Hope it helps!

     

    Best regards,
    Community Support Team_ Scott Chang

     

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

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi gomezc73 ,

     

    You may try the following expression

     

    Filter(Sales,Sales[Year_Month] in values('Year_Month From'[Year_Month])
     
    Filter(Sales,Sales[Year_Month] in values('Year_Month To'[Year_Month])
     

    Hope it helps!

     

    Best regards,
    Community Support Team_ Scott Chang

     

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi gomezc73 ,

     

    The filter in DAX is selecting on the context, not filtering on the table data, I created simple samples to illustrate this for you:

     

     

    Filter A = CALCULATE(SUMX('Sales','Sales'[Sales]),FILTER('Sales','Sales'[Type]="A"))

Total = SUMX('Sales','Sales'[Sales])

     

     

    For one , It filters out sums of type A , for another , it doe not have any filters. And your goal is to filter the entire table dynamically based on time, not to filter the data in the table. So you can use the simple SUMX function to do just that.

     

    Please feel free to correct me and provide more information if I have misunderstood you!

     

    Hope it helps!

     

    Best regards,
    Community Support Team_ Scott Chang

     

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

    • gomezc73's avatar
      gomezc73
      Helper V
      2 years ago

      Hi, thank you for your early response.

       

      I need explain how my reports works, because it is more complicated.

       

      I have a file of SALES/products by months, and the original request is compare sales in diferent months.

       

      To do that, i created two tables with the following comand:

       

      Year_Month From = VALUES(Sales[Year_Month])
      Year_Month To = VALUES(Sales[Year_Month])
       
       And i have a Slicer for each one
       
      The first table has a ACTIVE relationship with the SALES files and the second file has a INACTIVE relationshionship .
       
      To get the First of the first month i use a filter like this
      Filter(Sales,Sales[Year_Month] = selectedvalue('Year_Month From'[Year_Month])
       
      For the second month is use the same SUMX, but using a USERELATIONSHIP,
       
      Filter(Sales,Sales[Year_Month] = selectedvalue('Year_Month To'[Year_Month])
       
      In your response you use the Month from the same file SALES, but I can use it directly because i need compare two months.
       
      it works fine selecting only 1 month in each slicer.
       
      The issue is due the user wants select by example: Jan+Feb+Mar of 2022 in the first Slicer and compare it with Jan+Feb+Mar of 2023 (in the second Slicer)
       
      In that case my Measure doesn't work and i don't know how it can be modified to allow multiple selections.
       
      I really appreciate your help..
       
      regards