microsoft / microsoft/Analysis-Services

BPARules : Filter column and measure does not work properly

Open
#224 2 comments 0 reactions 0 assignees View on GitHub

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

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.