Filter in DAX with multiple criteria

[susansw]

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.......

[v-jiascu-msft]

@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

[GilbertQ]

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

[susansw]

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