cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
susansw
New Member

Filter in DAX with multiple criteria

My first time using DAX and I am trying to write some very basic queries and need to filter data based on two criteria.   I only require the data from the SalesFacts cube where the revenue stream is PRD OR SUP.    No matter how i change this i get either one of two errors.   It appears in the version i am using you can only have two criteria in the code.   I cannot find any answers on the web, having tried all options available.    Any idea would be great.

 

EVALUATE (     FILTER     (         SUMMARIZE         (         'SalesFacts'         , 'PeriodDate'[USDate]         , 'Salesperson'[Sales Team]        

       , "Salesperson Name",IF('Salesperson'[Sales Group] = "Dealer",'Order'[Salesperson],"")         , "InDirect Revenue", If('Salesperson'[Sales Group] = "Dealer", Sum('SalesFacts'[Line Value]),0)        )     , ‘SalesFacts’,  'RevenueStream'[Revenue Stream Code]= "PRD" && 'RevenueStream'[Revenue Stream Code]= "SUP"     ) ) ORDER BY 'PeriodDate'[USDATE]

 

Errors:

too many arguments were passed to the filter function when i use the above code.    When I remove the reference to 'SalesFacts', in the filter code then I get:  The value for the column salesperson cannot be determined in the current context:  Check that all columns referenced in exist.......

3 REPLIES 3
v-jiascu-msft
Microsoft
Microsoft

@susansw

 

Hi,

 

These suggestions below would help you. Please have a look.

  1. Where did you try this formula. If it was used in Power BI Desktop, EVALUATE isn’t necessary.
  2. No ORDER BY in Power BI Desktop as far as I know.
  3. DAX is a functional language. FILTER only has two parameters like this: FILTER(<table>,<filter>) .
  4. <filter> evaluate every row in <table>. Your <filter> has nothing with the <table> (the summarize supplied).
  5. Maybe your formula looks like this:
evaluate
SUMMARIZE (
    CALCULATETABLE (
        VALUES ( 'SalesFacts' ),
        'RevenueStream'[Revenue Stream Code] = "PRD",
        'RevenueStream'[Revenue Stream Code] = "SUP"
    ),
    'PeriodDate'[USDate],
    'Salesperson'[Sales Team],
    "Salesperson Name", sum(IF ( 'Salesperson'[Sales Group] = "Dealer", 'Order'[Salesperson], "" )),
    "InDirect Revenue", sum(IF (
        'Salesperson'[Sales Group] = "Dealer",
        SUM ( 'SalesFacts'[Line Value] ),
        0
    )))

Reference: https://msdn.microsoft.com/en-us/library/ee634396.aspx

https://msdn.microsoft.com/en-us/library/ee634966.aspx

 

Best Regards!

Dale

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

Hi there, are you trying to create a measure or a column?

When you want to use multiple criteria you can use the || which means "OR" when creating measures.

So you can do the following example:

 

 

Total Sales = CALCULATE(sum('Table1'[Sales]),'Table1'[Revenue Stream] = "PRD" || 'Table1'[Revenue Stream] = "SUP")

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

susansw
New Member

This is my first time with DAX and I am using the following code to filter data.   

 

EVALUATE (     

             FILTER     (         

                 SUMMARIZE         (         'SalesFacts'         , 'PeriodDate'[USDate]         , 'Salesperson'[Sales Team]        

       , "Salesperson Name",IF('Salesperson'[Sales Group] = "Dealer",'Order'[Salesperson],"")         , "InDirect Revenue", If('Salesperson'[Sales Group] = "Dealer", Sum('SalesFacts'[Line Value]),0)       

 )     , ‘SalesFacts’,  'RevenueStream'[Revenue Stream Code]= "PRD" && 'RevenueStream'[Revenue Stream Code]= "SUP"    

 )

)

ORDER BY 'PeriodDate'[USDATE]

 

If I have a table name in the filter - like above of 'SalesFacts' or even 'SalesPerson' I get an error:  Too many arguments were passed to the filter function.

If I remove the table name in the filter I get an error:  The value for the column Sales Group in table salesperons cannot be determined in the current context.   Check that all column referenced in the calculation expression exist..... 

 

Thank you

No matter what i do with the filter it get one of two errors.

 

If

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors