Forum Discussion

DanS446's avatar
DanS446
Helper I
2 years ago
Solved

Filter out data from one table but keep other data

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 SEL...
Need Help
  • DanS446's avatar
    DanS446
    2 years ago

    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's avatar
Shravan133
Super User
2 years ago

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's avatar
DanS446
Helper I
2 years ago

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