Forum Discussion
Impactful IndividualImplicit filters remove all records?
Impactful IndividualHello 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:
Community ChampionDouweMeer 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)
- v-kelly-msft
Community Support
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,
KellyDid 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
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 :).
- 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)?