cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mwaltercpa
Advocate II
Advocate II

Problems with matrix after using advanced query filters

i am trying to filter my sales table in query advanced filtering.  i want to only see accounts >=1 AND <=10, OR 12.

 

when i do this, i can load the filtered file and see the selected accounts fine.  however when i create a matrix, with any field, it shows blank reaults as if the table is empty?

 

if i revert back to a full file; all works fine?

1 ACCEPTED SOLUTION
v-haibl-msft
Microsoft
Microsoft

@mwaltercpa

 

I’m not quite sure how you apply the filter, but you can try to use CALCULATETABLE Function (DAX) to get the expected result.

Assuming we have a simple table like below.

Problems with matrix after using advanced query filters_1.jpg

 

We can use following DAX formula to create a new calculated table to filter the accounts.

Table2 = 
CALCULATETABLE (
    Table1,
    Table1[Accounts] >= 1
        && Table1[Accounts] <= 10
        || Table1[Accounts] = 12
)

Problems with matrix after using advanced query filters_2.jpg

 

Then drag the filtered columns in the new created Table2 into Matrix fields.

Problems with matrix after using advanced query filters_3.jpg

 

Best Regards,

Herbert

View solution in original post

2 REPLIES 2
mwaltercpa
Advocate II
Advocate II

Thanks, I will for sure use CALCULATETABLE in the future now!

 

I was using the Number filter from within Query, you drop down a field and one option is number filter, then I choose between, then choose 'advanced' to put in my filter conditions. 

 

I am not sure what I did different, but it works AND shows results in my table/matrix.

 

Thanks again!

v-haibl-msft
Microsoft
Microsoft

@mwaltercpa

 

I’m not quite sure how you apply the filter, but you can try to use CALCULATETABLE Function (DAX) to get the expected result.

Assuming we have a simple table like below.

Problems with matrix after using advanced query filters_1.jpg

 

We can use following DAX formula to create a new calculated table to filter the accounts.

Table2 = 
CALCULATETABLE (
    Table1,
    Table1[Accounts] >= 1
        && Table1[Accounts] <= 10
        || Table1[Accounts] = 12
)

Problems with matrix after using advanced query filters_2.jpg

 

Then drag the filtered columns in the new created Table2 into Matrix fields.

Problems with matrix after using advanced query filters_3.jpg

 

Best Regards,

Herbert

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors