cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DouweMeer
Post Prodigy
Post Prodigy

Implicit filters remove all records?

I've been working on an example from another user. I was initially answerring a question, till I bumped into some weird behavior of Power BI. Source table of the pitched problem:

Untitled.png

Request was to have a table with country on rows and on columns users. Then he wanted a value of 'Yes' when the combination exists and 'No' when it doesn't. The solution for him was with a twist as 'blank' was sufficient as a 'No'. Just like below:

Untitled2.png

But I wanted more. I wanted the 'No' on the blank values. So I continued. So I followed up with:

Untitled3.png

So my colleague suggested to state all return values as "No" to test the context. 

Untitled4.png

And I was like 

1u45dy.jpg

So apparently when implicit filters (including those from the filter pane) create a context of 0 records, whatever you do with explicit filters or via a DAX expression, will always remain 0. This way when calculation a countrows on an all('table') returns only values when the implicit filters have a context base larger than 0. 

Untitled5.png

So to screw with my brain even further, first a basic countrows with a filter on 'Country'[Country]:

 

Untitled6.png

 

And now changing the context to an all statement for the countrows:

 

Untitled7.png

 

So basically I have only 1 question. What do I need to do to make sure everywhere in this matrix with this source data the blank values are replaced with "No". 

 

Untitled8.png

12 REPLIES 12
az38
Super User II
Super User II

Hi @DouweMeer 

it is a veeeeeery looong read )))

but what I see from the first sught is your try to compare COUNTROWS() with a BLANK().

COUNTROWS() returns a whole number, so you need compare with 0, not BLANK() like

if(al=0, "No", al)

do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

Hi I would like to test it on my side and if you could share the pbix it would be better.
I haven't read it everything, but probably when the intercept of two different tables has a blank the measure is blank and therefore your measure is "No" only when you have resulting rows rather thank BLANK() 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




(in any case I gave you one kudo just for the style of the post 🙂 )





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@adetogni 

Due to company restrictions, I can't share you the Pbix (nor via OneDrive or another platform). All I can do is show you the tables. 

 

'Table' :

 

UserCountryStateemail
ABCIN  
XYZ MH 
DDDMU  
DDDPU  

 

'Country':

To be precise, my 'Country' table is a custom table with distinct ( selectcolumns ( 'Table' , "Country" , 'Table'[country] ) )

 

Country
IN
 
MU
PU

 

I set a relationship between 'Table'[Country] and 'Country'[Country]. By default this is a 1 on 1 relationship.

If you know the answer, that would be great. And thanks for the kudo 🤝. I would advise you to read the rest if you have some time.

Hi @DouweMeer

First you need to create a table with the column of "user",using a dax as below:

 

User Table = DISTINCT('Table'[User])

 

Annotation 2020-02-25 153031.png

Then you need a measure as below:

 

Measure = CALCULATE(DISTINCTCOUNTNOBLANK('Table'[Country]),FILTER('Table','Table'[User] in FILTERS('User Table'[User])))+0

 

Finally you will see:

 

Annotation 2020-02-25 153226.png

 

For the related .pbix file,pls click here.

 

Best Regards,
Kelly
 
Did I answer your question? Mark my post as a solution!

 

Hello @v-kelly-msft 

Never knew you can put non related fields in a matrix. And by using the principle of dynamic filters into the context is very clever. For that I'll give you a kudo. 

 

Although this 'solves' the case problem, I consider this cheating 😊. There is no way to scale this up, unless you want to create dozens of new tables for all filters one has in a model 😅. Also, you lose the filter functionalities Power BI has, so why wouldn't I use Tableau from this point on? If you have another smart insight on this, I'd like to test it. 

 

Sadly enough I have to stick to my previous statement:

 

Untitled8.png

Stachu
Community Champion
Community Champion

@DouweMeer challenge accepted 😉
as far as I can tell user is actually not relevant here

Measure = 
VAR __Country = SELECTEDVALUE(Country[Country])
VAr __NrOfRows = COUNTROWS(FILTER('Table','Table'[Country]=__Country))
RETURN
IF(__NrOfRows>0,"Yes","No")

which gives this:
Capture.PNG
Country[Country] is in the rows, Table[User] is in the columns, works with 1:Many single relationship where Country filters Table and with 1:1 with bidirectional relationship (although I would definitely recommend using 1:many here)

 



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

Hello @Stachu 

Thank you for accepting the challenge :). 

When reading through your solution, I doubted the results but tried anyway. The problem I saw was that you don't actually change the context of the matrix. If you would remove the 'Table'[Country] category in my own printscreens, you would see this return the same return values as your solution. 

Your solution with an extra filter on 'Table'[State] :

Untitled.png

As you can see, this is the same behavior as in my initial post. I'm afraid this is not the solution for my issue. If you can be bothered with giving it another shot, that would be great :). 

Hi @DouweMeer ,

 

My method only needs to create one table to put all the column headers in it,no need to create multiple tables,as there may be multiple rows that cant be matched in your matrix,those cant be matched will show blank in the matrix, there's no better ways to achieve it.Try my method,you will find it useful.

 

 

Best Regards,
Kelly
 
Did I answer your question? Mark my post as a solution!
Stachu
Community Champion
Community Champion

From what I tested if you use star schema with the field in the columns being in the separate table. However with this setup whatever you put in rows has to come from the same table where filtered by the dimension from columns, otherwise you get cartesian product of the dimensions

Measure = 
VAR __User = SELECTEDVALUE('Users'[User])
VAR __NrOfRows = COUNTROWS(FILTER('Table', 'Table'[User]=__User))
RETURN
IF(__NrOfRows>0,"Yes","No")

 Capture.PNG



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

@v-kelly-msft and @Stachu 

This does seem to be solution indeed. Although, with multiple columns, you still end up with a large second table when the original is getting larger and larger. 

 

Untitled.png

But if you would make a selection in the visual (red), only the distinct non related table is filtered (red), but the original isn't filtered of course (green).

 

Untitled2.png

I guess there is no way to manipulate the filter statement of the implicit filter to come after the DAX expression (suggestion) like with the explicit filter (assumption)? 

@az38 

**bleep** happens when you find something quirky. You can't get away with just but a statement 😁.

Did tried yours, however the statement =blank() does actually work. See below. 

Untitled.png

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

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.