Forum Discussion
Filtering a MAtrix table on sublevel vlaue
Hi all,
I have dax question.
I would like to filter my Matrix table based on the value of a sublevel.
Current Set up:
Level 1 : ParentAccount
Level 2: Account
Level 3: Contract
I created a Delta measure that calculates the difference between 2 selected dates.
"P1" & "P2".
Next I would like the filter my rows based on the value of Account! If Delta of Account = 0 then it shouldn't show.
This is where I'm stuck. Anyone an Idea how I can filter this Matrix?
as an example, this line should not show.
Robin
Hi, Anonymous
According to your description, I can understand what you want to achieve, you want to make the rows that Delate is 0 and is in the scope of [ParentAccount] to disappear in the Matrix, right? You can try my steps:
This is my test data based on your Matrix:
I created these two measures:
Delta = SUMX('Table',[P2]-[P1])Flag = IF( ISINSCOPE('Table'[Parent Account]), IF( [Delta]=0,0,1),1 )Then I created a Matrix and place it like this:
As you can see, the row in which Delta is 0 can still appear in the Matrix.
Then I apply a filter to this Matrix, like this:
Then the row disappeared, and you can get what you want.
You can download my test pbix file here
If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.
How to Get Your Question Answered Quickly
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Anonymous , isinscope and checking value should help
https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/
if( isinscope(Table[account]) && Max(Table[account]) = "Abc", [Measure1], [measure2])
Hi, Anonymous
According to your description, I can understand what you want to achieve, you want to make the rows that Delate is 0 and is in the scope of [ParentAccount] to disappear in the Matrix, right? You can try my steps:
This is my test data based on your Matrix:
I created these two measures:
Delta = SUMX('Table',[P2]-[P1])Flag = IF( ISINSCOPE('Table'[Parent Account]), IF( [Delta]=0,0,1),1 )Then I created a Matrix and place it like this:
As you can see, the row in which Delta is 0 can still appear in the Matrix.
Then I apply a filter to this Matrix, like this:
Then the row disappeared, and you can get what you want.
You can download my test pbix file here
If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.
How to Get Your Question Answered Quickly
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
