Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
DanS446
Helper I
Helper I

Filter out data from one table but keep other data

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

DanS446_1-1716499105333.png

 

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.

1 ACCEPTED SOLUTION
DanS446
Helper I
Helper I

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.

View solution in original post

3 REPLIES 3
DanS446
Helper I
Helper I

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
Resolver I
Resolver I

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.

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.