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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Filter on a filtered result

Hello, good morning.

I would like to know if there is any way to apply a filter on a result that has been applied to it before.

I have two queries:

1. NOTIFICATIONS

Dulce_Gamez4_1-1603707659667.png

2. RESIDENTS

2pbi.png

What I want to accomplish is that when I filter on Person ID , I get the Communication data Comunicación (See Notifications [ID][DT_Notificacion][ DT_Prueba] [DT_Sintima] [DT_Cuarentena] and Resident Consultation [Plant]. And in two tables next to having the information of the other IDs of people who shared the same Residence and the same Plant, but that this is done automatically when I select the Person ID filter. ID de Persona.

So it is currently but I have to manually add the residence and the plant to the two tables:

5pbi.png

I would like to know if it is possible to automatically put the Residence and Plant that is filtered anteriomente when selecting the person ID:

3pbi.png

Thank you very much in advance.

1 ACCEPTED SOLUTION

Hello @Dulce_Gamez4 ,

I think you need to have all the tables filtered by the ID, so I did the following:

  • Created a disconnected table
  • Added a segmentation with the table disconnected
  • Synchronize the previous targeting with notification targeting
  • The following measures have been added:
Residencia_Selection = 
VAR Selected_Person =
    VALUES ( IDPERSONA[IDPERSONA] )
VAR tempTable =
    FILTER (
        ALL ( Residentes[IDPERSONA]; Residentes[Planta]; Residentes[RESIDENCIA] );
        Residentes[IDPERSONA] IN Selected_Person
    )
VAR Columns_selection =
    SELECTCOLUMNS ( tempTable; "Resi"; Residentes[RESIDENCIA] )
VAR FIlter_Residents =
    FILTER ( ALL ( Residentes ); Residentes[RESIDENCIA] IN Columns_selection )
VAR Filter_Persons =
    FILTER (
        FIlter_Residents;
        Residentes[IDPERSONA] = SELECTEDVALUE ( Residentes[IDPERSONA] )
    )
RETURN
    MAXX ( Filter_Persons; Residentes[IDPERSONA] )

Planta_Selection = 
VAR Selected_Person =
    VALUES ( IDPERSONA[IDPERSONA] )
VAR tempTable =
    FILTER (
        ALL ( Residentes[IDPERSONA]; Residentes[Planta]; Residentes[RESIDENCIA] );
        Residentes[IDPERSONA] IN Selected_Person
    )
VAR Columns_selection =
    SELECTCOLUMNS (
        tempTable;
        "Residencia_selection"; Residentes[RESIDENCIA] & Residentes[Planta]
    )
VAR FIlter_Residents =
    FILTER (
        ADDCOLUMNS (
            ALL ( Residentes );
            "Residencia_Planta"; CONCATENATE ( Residentes[RESIDENCIA]; Residentes[Planta] )
        );
        [Residencia_Planta] IN Columns_selection
    )
VAR Filter_Persons =
    FILTER (
        FIlter_Residents;
        Residentes[IDPERSONA] = SELECTEDVALUE ( Residentes[IDPERSONA] )
    )
RETURN
    MAXX ( Filter_Persons; Residentes[IDPERSONA] )

This measure then has filters on the tables, and then hide the segmentation based on the disconnected table.

Using this option you can have multiple user selections, keep in mind that the trick here is to synchronize both altough slicers that are in disconnected tables.

In the PBIX file I present both slicers, but you must hide one in the final version of the report.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

10 REPLIES 10
MFelix
Super User
Super User

Hi @Anonymous ,

 

How do you have the relationship between tables? In theory if you have a dimension table with the Residencia and Planta tha makes the relationship between the two table you should be abble to select the slicer and the tables below update.

 

Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable
Anonymous
Not applicable

Thank you for your answer, I tried to create the dimension table but I still can't make it work.  

 

I have created a simplified pbix

https://myuax-my.sharepoint.com/:u:/g/personal/dgamez_uax_es/EadcEhhCEutCqqMUjPop6dwBxIxrNXxat5crM0p...

 

covid_ex1.pngvocid_ex2.png

 

 

 

Thank you very much!

This is possible but fyi that your pbix file has multiple errors in the visuals. Please attach a version w/o those.  To do this, a measure that checks the Residencia and Planta match the ones for the selected person, and then use that measure to filter the visuals in the filter panel.

 

Regards,

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Anonymous
Not applicable

Hello @mahoneypat, thankyou for your response,

I tried to create the new measure, but it doesn't work 😞

MedidaResidencia ?

var PersonSeleted ? SELECTEDVALUE(Residents[IDPERSONA])

var Residence ? CALCULATE(MAX(Residents[RESIDENCIA]), FILTER(ALLSELECTED(Residents),Residents[IDPERSONA]-PersonSeleted))

Return

IF(MAX(Residents[RESIDENCE]) - Residence ,1, 0)

Here's the link to the new pbix

-OneDrive

https://myuax-my.sharepoint.com/:f:/g/personal/dgamez_uax_es/EqkMAaYM30lFnToIh-CSVKIB082tEZLaxB2VsJ4...

-Drive

https://drive.google.com/drive/folders/17cKFp6sk1di1PK4d2XrpjUSOoG_lnOH9?usp=sharing

Thank you for your help

Hi @Anonymous ,

 

The link does not work.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

Here is one way to do this.

 

1.  Make a disconnected table with your ID values with 

SlicerIDs = DISTINCT(Residentes[IDPERSONA])
 
and use that column in your ID slicer at the top
 
2.  Make a measure like this
Same Residence = var selectedID = SELECTEDVALUE(SlicerIDs[IDPERSONA])
var selectedresidence = CALCULATE(MIN(Residentes[RESIDENCIA]), ALL(Residentes), Residentes[IDPERSONA] = selectedID)
return if(MIN(Residentes[RESIDENCIA]) = selectedresidence,1,0)
 
3.  Add the measure above to the Filter Panel on your table visual with residences and set it to "is" 1
 
Note: I started using the ID column in the Notificaciones table in the DISTINCT but it wasn't working.  Do all the IDs in that table have matching values in the Residences table?  You can use the same approach for the Planta visual.
 
Regards,
Pat




Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Hello @Dulce_Gamez4 ,

I think you need to have all the tables filtered by the ID, so I did the following:

  • Created a disconnected table
  • Added a segmentation with the table disconnected
  • Synchronize the previous targeting with notification targeting
  • The following measures have been added:
Residencia_Selection = 
VAR Selected_Person =
    VALUES ( IDPERSONA[IDPERSONA] )
VAR tempTable =
    FILTER (
        ALL ( Residentes[IDPERSONA]; Residentes[Planta]; Residentes[RESIDENCIA] );
        Residentes[IDPERSONA] IN Selected_Person
    )
VAR Columns_selection =
    SELECTCOLUMNS ( tempTable; "Resi"; Residentes[RESIDENCIA] )
VAR FIlter_Residents =
    FILTER ( ALL ( Residentes ); Residentes[RESIDENCIA] IN Columns_selection )
VAR Filter_Persons =
    FILTER (
        FIlter_Residents;
        Residentes[IDPERSONA] = SELECTEDVALUE ( Residentes[IDPERSONA] )
    )
RETURN
    MAXX ( Filter_Persons; Residentes[IDPERSONA] )

Planta_Selection = 
VAR Selected_Person =
    VALUES ( IDPERSONA[IDPERSONA] )
VAR tempTable =
    FILTER (
        ALL ( Residentes[IDPERSONA]; Residentes[Planta]; Residentes[RESIDENCIA] );
        Residentes[IDPERSONA] IN Selected_Person
    )
VAR Columns_selection =
    SELECTCOLUMNS (
        tempTable;
        "Residencia_selection"; Residentes[RESIDENCIA] & Residentes[Planta]
    )
VAR FIlter_Residents =
    FILTER (
        ADDCOLUMNS (
            ALL ( Residentes );
            "Residencia_Planta"; CONCATENATE ( Residentes[RESIDENCIA]; Residentes[Planta] )
        );
        [Residencia_Planta] IN Columns_selection
    )
VAR Filter_Persons =
    FILTER (
        FIlter_Residents;
        Residentes[IDPERSONA] = SELECTEDVALUE ( Residentes[IDPERSONA] )
    )
RETURN
    MAXX ( Filter_Persons; Residentes[IDPERSONA] )

This measure then has filters on the tables, and then hide the segmentation based on the disconnected table.

Using this option you can have multiple user selections, keep in mind that the trick here is to synchronize both altough slicers that are in disconnected tables.

In the PBIX file I present both slicers, but you must hide one in the final version of the report.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

Thanks a lot! worked 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.