Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.......
Hi,
These suggestions below would help you. Please have a look.
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
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")
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
80 | |
67 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |