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.
Hello All,
I'm trying to calculate total hours for only certain part numbers. I'm trying to filter them using calculate as below:
Reqhrs = Calculate ([Total Hours], New[Part Numbers] = 1111 && 11111)
I'm getting the desired result if I filter for just 1 part number but if I enter 2 or more, the result is blank.
Any help would be appreciated. Thanks
Solved! Go to Solution.
You are using "and" but you should be using "or". Any given product can't be part number 1111 and also be part number 11111
Here are two options
Reqhrs = Calculate ([Total Hours], New[Part Numbers] = 1111 || 11111)
Reqhrs = Calculate ([Total Hours], New[Part Numbers] in {1111,11111})
The correct syntax is:
Reqhrs = Calculate ([Total Hours], New[Part Numbers] = 1111 || New[Part Numbers] = 11111)
But CALCULATE has a limit on how many boolean filters you can do. You should consider using filter. Much more flexible, and this is what DAX is doing in the background anyway.
Reqhrs =
CALCULATE (
[Total Hours],
FILTER (
New,
New[Part Numbers] = 1111
|| New[Part Numbers] = 11111
)
)
FILTER will let you filter fields from multiple tables. You cannot do that in CALCULATE without FILTER(). DAX isn't smart enough to translate your boolean logic to include multiple tables.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingYou are using "and" but you should be using "or". Any given product can't be part number 1111 and also be part number 11111
Here are two options
Reqhrs = Calculate ([Total Hours], New[Part Numbers] = 1111 || 11111)
Reqhrs = Calculate ([Total Hours], New[Part Numbers] in {1111,11111})
@MattAllington Thanks for your reply. Using the two options you mentioned above is giving me two different results. The first option is giving me the total for all part numbers, the second one seems to be accurate. Upon looking more, is it possible that to use the first option I'll have to repeat the table name after every OR ( || ) sign?
The correct syntax is:
Reqhrs = Calculate ([Total Hours], New[Part Numbers] = 1111 || New[Part Numbers] = 11111)
But CALCULATE has a limit on how many boolean filters you can do. You should consider using filter. Much more flexible, and this is what DAX is doing in the background anyway.
Reqhrs =
CALCULATE (
[Total Hours],
FILTER (
New,
New[Part Numbers] = 1111
|| New[Part Numbers] = 11111
)
)
FILTER will let you filter fields from multiple tables. You cannot do that in CALCULATE without FILTER(). DAX isn't smart enough to translate your boolean logic to include multiple tables.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingCovering 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 |
---|---|
107 | |
99 | |
76 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |