Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
The following formula works, but I'm trying to better the approach a little since strings around it can change dynamically at the source, but the integer code pertaining to a category (233333, 455555 in the below example) will not.
Current formula :
Solved! Go to Solution.
You cannot put a true/false statements under CALCULATE. There are very specific conditions that can be written like this but they really are not true/false statements since the engine expands them automatically into tables. Because - let me reiterate it - only tables can be filters under CALCULATE. So, instead of CONTAINS in there you should turn it into a filter on a table and put the table instead. But I think you should use a different function here, CONTAINSSTRING, like so:
CALCULATE(
[Total Time],
FILTER(
VALUES( 'Fact Table'[Category] ),
OR(
CONTAINSSTRING(
'Fact Table'[Category],
"310251"
),
CONTAINSSTRING(
'Fact Table'[Category],
"320251"
)
)
)
)
Thank you. Can you explain your logic behind using VALUES function here?
Since I was thinking along the lines of :
You cannot put a true/false statements under CALCULATE. There are very specific conditions that can be written like this but they really are not true/false statements since the engine expands them automatically into tables. Because - let me reiterate it - only tables can be filters under CALCULATE. So, instead of CONTAINS in there you should turn it into a filter on a table and put the table instead. But I think you should use a different function here, CONTAINSSTRING, like so:
CALCULATE(
[Total Time],
FILTER(
VALUES( 'Fact Table'[Category] ),
OR(
CONTAINSSTRING(
'Fact Table'[Category],
"310251"
),
CONTAINSSTRING(
'Fact Table'[Category],
"320251"
)
)
)
)
User | Count |
---|---|
22 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
25 | |
13 | |
11 | |
9 | |
6 |