Filter out data from one table but keep other data

[DanS446]

Greetings, i have 2 tables with a relation for their id number called "NUP"

A calculated column to check if the smaller table IDs matches the big table IDs

NUP match = IF(Mail[NUP] IN SELECTCOLUMNS('NUP company','NUP company'[NUP]), "NUP company", "no")

So lets say from 1000 IDs, 10 are "company ID". Now, the smaller table has a "Assigned" column so certain IDs are assgined to certain people. I have a general slicer to filter only "company ID".

What i want to do, is have a filter/slicer to select an user name, and filter out IDs that are different than the person selected (those being in the 10 company IDs) but keep the other 990 IDs that are general. Im trying to make a filter that has no interaction with the visual table, but instead the table has a measure filter. So far i got it to give me the general IDs and all the user IDs with something like this.

IF(SELECTEDVALUE('NUP company'[Assigned])=BLANK() || SELECTEDVALUE('NUP company'[Assigned]) in VALUES('NUP company'[Responsable]),1,0)

So it always picks the regular IDs wich will have blank as assigned, and any of the user IDs, not sure how to make that a selection on a dropdown menu so it filters out company IDs that are not the user selected.

[DanS446 · Accepted solution]

I solved it follwing user jaideepnema solution on another post as an example:

- Created a new table with Assigned column as a class table, so cointains all names on Assign plus a blank (" ").

- Use that table as a slicer

- Copy over the Assigned values to main table with a calculated column:

Assigned = LOOKUPVALUE('NUP company'[Assigned],'NUP company'[NUP], Mail[NUP] )

so now we have: all IDs>company IDs>Assigned IDs on the same table (else you have to actually put Assigned on the visual table so the filter works)

- Create a measure to filter the visual table:

Filter = IF(SELECTEDVALUE('Mail'[Assigned])=BLANK() || SELECTEDVALUE(Class[Assigned])=SELECTEDVALUE(Mail[Assigned]) || SELECTEDVALUE(Class[Assigned])=BLANK() ,1,0)

- Use measure as a filter for main visual table (is = 1)

This shows all data when no selection is made on Assigned slicer, but when you select an user, it filters out only the company IDs that have other users assigned, keeping the rest of the data. The only problem is that selecting more than one user results in all data being showed, not a problem in my application, will only select one at a time.

Another related measure:

Not total = CALCULATE(COUNTROWS(Mail), FILTER(Mail,[Filter]))+0

I had a "total notifications" measure, making it calculate using filter for the main table and the measure works on counting the same data as the measure filter does, before it will ignore it since it usually works on sliced data and not a measure filter.

[Shravan133]

Step 1: Data Model

Ensure your tables are related correctly in the data model:

• Mail table with a column NUP.
• NUP company table with columns NUP and Assigned.

  The relationship should be based on the NUP column.

  Step 2: Create a Calculated Column to Identify Company IDs

  In the Mail table, create a calculated column to check if the NUP exists in the NUP company table.

  NUP Match = IF ( Mail[NUP] IN SELECTCOLUMNS('NUP company', 'NUP company'[NUP]), "NUP company", "no" )

  Step 3: Create a Slicer for Assigned Users

  Create a slicer based on the Assigned column from the NUP company table. This allows you to select users.

  Step 4: Create a Measure to Filter Data

  Create a measure in the Mail table to filter out IDs based on the slicer selection.

   

  Filter Measure = IF ( ISFILTERED('NUP company'[Assigned]), IF ( SELECTEDVALUE('NUP company'[Assigned]) = BLANK() || SELECTEDVALUE('NUP company'[Assigned]) IN VALUES('NUP company'[Assigned]), 1, 0 ), 1 )

  Step 5: Apply the Measure as a Visual Level Filter

  In your visual (e.g., a table or matrix), apply the Filter Measure as a visual-level filter. Set the filter to show only where the measure equals 1.

  Step 6: Additional Configuration

  Ensure that the slicer does not interact with the visual directly. You can do this by adjusting the visual interactions in Power BI.

  1. Select the slicer.
  2. Click on the “Format” pane.
  3. Choose “Edit Interactions”.
  4. Set the interaction between the slicer and your visual to “None” (the crossed-out filter icon).

     Result

     With this setup:

     • The slicer allows you to select a user.
     • The measure filters the visual to show:
       • All general IDs (which have Assigned as BLANK).
       • The selected user's IDs from the NUP company table.

[DanS446]

Does not work, you just fed my post into an AI