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 Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors