Implicit filters remove all records?

[DouweMeer]

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:

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:

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

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

And I was like 

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. 

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

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

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". 

[az38]

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)

[Anonymous]

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() 

[Anonymous]

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

[DouweMeer]

@Anonymous 

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' :

User	Country	State	email
ABC	IN
XYZ		MH
DDD	MU
DDD	PU

'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.

[v-kelly-msft]

Hi @DouweMeer ，

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

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

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:

For the related .pbix file,pls click here.

Best Regards,
Kelly

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

[DouweMeer]

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:

[Stachu]

@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:

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)

[DouweMeer]

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] :

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 :). 

[v-kelly-msft]

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]

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")

 

[DouweMeer]

@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. 

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).

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)? 

[DouweMeer]

@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.