microsoft / microsoft/Analysis-Services
BPARules : Filter column and measure does not work properly
Nobody has claimed this yet.
- Dominant language
- C#
- Stars
- 732
- Forks
- 433
- PR merge metrics
- No merged PRs in 30d
Description
Hello,
from my point of view following rules does not work properly:
- Filter column values with proper syntax
- Filter measure values by columns, not tables
In case the syntax of CALCULATE or CALCULATETABLE is simple, then it works fine. But let's imagine I have this piece of DAX expression:
CALCULATE (
SUMX (
Sales,
Sales[Net Price] * Sales[Quantity]
),
FILTER(
Sales,
Sales[Currency Code] = "USD"
)
)
or
CALCULATE (
SUMX (
Sales,
Sales[Net Price] * Sales[Quantity]
),
FILTER (
Sales,
[NetSalesPerOrderDate] > 100
)
)
Then both rules are not violated, but I think they should be. Because of that I suggest to update rule definitions.
Filter column values with proper syntax
RegEx.IsMatch(Regex.Replace(Expression, "\s", String.Empty),"(?i)CALCULATE\(.*(?i),(?i)FILTER(?i)\([']?.+[']?,[']?.+[']?\[.*\].*")
or
RegEx.IsMatch(Regex.Replace(Expression, "\s", String.Empty),"(?i)CALCULATETABLE\(.*(?i),(?i)FILTER(?i)\([']?.+[']?,[']?.+[']?\[.*\].*")
Filter measure values by columns, not tables
RegEx.IsMatch(Regex.Replace(Expression, "\s", String.Empty),"(?i)CALCULATE\(.*(?i),(?i)FILTER(?i)\([']?.+[']?,\[.*\].*")
or
RegEx.IsMatch(Regex.Replace(Expression, "\s", String.Empty),"(?i)CALCULATETABLE\(.*(?i),(?i)FILTER(?i)\([']?.+[']?,\[.*\].*")
The idea is the same for both rules. At first I replace all the whitespaces from the DAX expression. Then I updated Regex to search. I know that the rule is violated when the FILTER is applied at Filter parameter of CALCULATE or CALCULATETABLE functions.
The Filter parameter is always the second parameter for both functions. So I know that preceding character is a comma. And that is exactly what this regex string is doing.
I did some testing on my test dataset and it has been working fine. But I will appreciate any feedback from your side.
Regards,
Jiri
Contributor guide
No contributing guide indexed for this repository
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
Research direction
Start by locating the BPARules definitions for the two filter rules and reviewing how DAX expressions are normalized before matching. Compare the existing behavior with the two CALCULATE and CALCULATETABLE examples, then verify that the proposed cases are flagged without breaking simple expressions; the issue mentions testing against a test dataset but names no automated test.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- csharp
- Domain
- tooling
- Issue type
- Bug
- Difficulty
- 3/5
- Estimated time
- 1-2 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 42/100