Latest Enterprise DNA Initiatives


Filter a column based on another column

Hello PBI lovers,

actually a quite simple thing you should think, but somehow I’m standing in the forest and can no longer see the trees. Can you help?

I have a fact table and two dimension tables which are based on the first one.
dim_company and dim_ICPartnerCompany.

All filters work fine as far as they can be seen in the two tables above.

In the table below I want to show the opposite result without using additional slicers.

That means, if I choose Company = 160 and IC PartnerCompany = 180, all results for this selection should show up in the above table. → Done

In the table below, the cloumn company should be filtered by 180, and the IC PartnerCompany by 160.

Unfortunately, all my attempts so far come up empty and give errors or don’t work properly : vice versa = FILTER(VALUES(FactTable[Company]), SELECTEDVALUE(dim_ICPartnerCompany[IC Partner Company]))

Can you help? Find the PBIX file in the appendix.

Thanks in advance.

Danilo
Test_IC_Calculation.pbix (58.0 KB)

Hi @d.mesch

Here is the solution for your requirement.

Created disconnected tables for slicers and measures for visual level filter.

image

Visual level filter for Table1 and Table2

Visual level filter for Table3

Here is a good radacad video on this

Hello @d.mesch , just following up if the response above help you solve your inquiry?

If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query.

Hi @d.mesch, we’ve noticed that no response has been received from you since a few days ago.

We just want to check if you still need further help with this post?

In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

@cmalone1112

Here the requirement is different. Radacad’s video is a different issue.

Hi @d.mesch, due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.

Kindly take time to answer the Enterprise DNA Forum User Experience Survey, we hope you’ll give your insights on how we can further improve the Support forum. Thanks!

@EnterpriseDNA

Marked solution is not relevant to this Issue. Radacad’s video is on a different issue.

I already explained how to resolve this issue. Please check

Hello @Rajesh Rajesh,

sorry for the late answer, but i was in between in another urgent project.
sorry also for the incorrect solution message, the system has assigned it due to my absence.

I tried your solution, but somehow I don’t get it to work.
I build up the tables, I build up the measures. But how I need to set the filters correctly to get it work? Can you still describe/show me this way.

Txh in advance
Danilo

@Rajesh

sometimes thinking twice helps :slight_smile:

It is working for me now and I’m very happy.
thanks for your help and hope to see you soon.
Danilo

@Rajesh
hmmm… it seems that i was too early. The slicer for the IC Partner Company does not filter the IC Partners for me. It does nothing at all. If I have several IC partners for one company, they are all displayed, no matter what I set.

Could you kindly tell me again how to set the filters so that they work?

In all tables only the company and IC partner should be displayed, which is set in the slicer.

Thx Dan

@d.mesch

please share the sample data with several IC partners.

Test_IC_Calculation.pbix (69.1 KB)
@Rajesh

1 = slicer Company → works fine
2 = slicer IC Partner → not working
3 = FactTable → possible matches

Somehow I did not manage to set the slicer the right way.
As usual, I would expect that only the values that are set via the slicers are displayed in the two right tables.

Thx Dan

@d.mesch

  1. Do you want to select multiple values in slicers or only one value ?
  2. If you select 160 from Company and 100 from IC Partner , third table we’ve to show 100 company and 160 ICP Partner right ? but we don’t have company 100, in this case how you want to show third table ?

@Rajesh

yeah you’re right. My mistake. I wanted to be quick and did not build the data quite correctly. The dim_Company corresponds 100% to the dim_ICPartner Company.

I have added the data and reattached.

For your 1. question - In everyday work, a single selection will suffice.
Where would be the difference to a multiple selection in the structure of the formulas?

Test_IC_Calculation.pbix (69.4 KB)

Bumping this post for more visibility.

Hi @d.mesch! We noticed that your inquiry was left unsolved for quite some time now.

Looks like your inquiry was out of the experts and users’ bounds.

We strive to answer ALL inquiries in the forum. However, if you are sure that you provided all pertinent context to your concerns and read how to use the forum more effectively and still find your question unanswered, you can check out tutorials to learn this yourself as your membership also comes with relevant resources that may help you with your Power BI education.

While our users and experts do as much as reasonable to help you with your inquiries, not all concerns can be attended to especially if there are some learnings to be done. Thank you!