cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PowerBIOrg
Resolver I
Resolver I

Filtering issue

Hi all,

 

I have a PatientID slicer/filter and a check box that mentioned "Included Dependent" in my report. Blue Table is my table structure. Green Table shows the desired output when I filtered PatientID and whether or not I check the "Included Dependent".

PowerBIOrg_0-1656052378629.png

For example, when I filter 'A1' in PatientID and I checked the "Included Dependent", then it will return 4 Patients records (A0, A2, A3, A4). On the other hand, if I didn't checked the "Included Dependent", then it only show 'A1' record. 

 

Anyway that I can apply to all visuals in my report?

 

Thank you. 

 

1 ACCEPTED SOLUTION

Hi @PowerBIOrg ,

 

Here's my solution.

1.Create a calculated table to get the unique Patient ID for the slicer.

Table 2 = DISTINCT('Table'[PatientID])

vstephenmsft_0-1656491861601.png

 

2.Create another table with the following two values for the slicer too by entering data.

vstephenmsft_1-1656491884786.png

 

3.There're no relationships among tables.

vstephenmsft_2-1656491982211.png

 

4.Create a measure as a visual level filter.

Measure = 
SWITCH (
    SELECTEDVALUE ( 'Table 3'[Type] ),
    "Included Dependent",
        IF (
            CALCULATE (
                MAX ( 'Table'[EmployeeID] ),
                FILTER (
                    ALLSELECTED ( 'Table' ),
                    [EmployeeID] = MAX ( 'Table'[EmployeeID] )
                        && [PatientID] IN ALLSELECTED ( 'Table 2'[PatientID] )
                )
            )
                = MAX ( 'Table'[EmployeeID] ),
            1
        ),
    "Not Included Dependent",
        IF (
            CALCULATE (
                MAX ( 'Table'[PatientID] ),
                FILTER (
                    ALLSELECTED ( 'Table' ),
                    [EmployeeID] = MAX ( 'Table'[EmployeeID] )
                        && [PatientID] IN ALLSELECTED ( 'Table 2'[PatientID] )
                )
            )
                = MAX ( 'Table'[PatientID] ),
            1
        )
)

 

5.Create two slicers, a table visual as follows.

vstephenmsft_3-1656492075353.png

 

6.Put the measure into the table visual level filters, set show items when the value is 1.

vstephenmsft_4-1656492117965.png

 

Now, when you select 'Included Dependent', 'A0' and 'B0', the result is below.

vstephenmsft_5-1656492177524.png

 

When you select 'Not Included Dependent', 'A0' and 'B0', the result is below.

vstephenmsft_6-1656492292190.png

You can check more details from my attachment.

 

 

Best Regards,

Stephen Tao

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
v-stephen-msft
Community Support
Community Support

Hi @PowerBIOrg ,

 

One question, where is A4 in your input table?

vstephenmsft_0-1656409349841.png

 

According to your description, I temporarily get the following results.

vstephenmsft_1-1656409480438.png

You can filter the Paitent ID.

vstephenmsft_3-1656409572148.png

 

You can download my attachment for details.

 

 

Best Regards,

Stephen Tao

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

Hi, I'm sorry that making you all confuse. I actually want the output like the following example: 

If I choose A0, A1, A2 or A3 patient and ticked "Included Dependent", it will return the records of Employee A0. For example: if I choose A2, then it will show A0 Employees records (which included all A0, A1, A2, A3 patients records).

If I choose A0, A1, A2 or A3 patient and ticked "Not Included Dependent", it will return the records of selected patient only. For example: if I choose A1, then it will show A1 patient records only. 

Hi @PowerBIOrg ,

 

Here's my solution.

1.Create a calculated table to get the unique Patient ID for the slicer.

Table 2 = DISTINCT('Table'[PatientID])

vstephenmsft_0-1656491861601.png

 

2.Create another table with the following two values for the slicer too by entering data.

vstephenmsft_1-1656491884786.png

 

3.There're no relationships among tables.

vstephenmsft_2-1656491982211.png

 

4.Create a measure as a visual level filter.

Measure = 
SWITCH (
    SELECTEDVALUE ( 'Table 3'[Type] ),
    "Included Dependent",
        IF (
            CALCULATE (
                MAX ( 'Table'[EmployeeID] ),
                FILTER (
                    ALLSELECTED ( 'Table' ),
                    [EmployeeID] = MAX ( 'Table'[EmployeeID] )
                        && [PatientID] IN ALLSELECTED ( 'Table 2'[PatientID] )
                )
            )
                = MAX ( 'Table'[EmployeeID] ),
            1
        ),
    "Not Included Dependent",
        IF (
            CALCULATE (
                MAX ( 'Table'[PatientID] ),
                FILTER (
                    ALLSELECTED ( 'Table' ),
                    [EmployeeID] = MAX ( 'Table'[EmployeeID] )
                        && [PatientID] IN ALLSELECTED ( 'Table 2'[PatientID] )
                )
            )
                = MAX ( 'Table'[PatientID] ),
            1
        )
)

 

5.Create two slicers, a table visual as follows.

vstephenmsft_3-1656492075353.png

 

6.Put the measure into the table visual level filters, set show items when the value is 1.

vstephenmsft_4-1656492117965.png

 

Now, when you select 'Included Dependent', 'A0' and 'B0', the result is below.

vstephenmsft_5-1656492177524.png

 

When you select 'Not Included Dependent', 'A0' and 'B0', the result is below.

vstephenmsft_6-1656492292190.png

You can check more details from my attachment.

 

 

Best Regards,

Stephen Tao

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks Stephen!

 

Though it didn't meet my desired output for "Not Included Dependent" part, so I modified the query as following: 

 

 

IF ( 
    CALCULATE(MAX('Table'[PatientID]), 
        FILTER ('Table', [PatientID] IN ALLSELECTED ( 'Table 2'[PatientID] ) ) )
    = MAX ( 'Table'[PatientID] ),
    1
 )

 

 

PowerBIOrg_0-1656555760700.png

 

amitchandak
Super User
Super User

@PowerBIOrg , You can create a new column and use that in the matrix

 

if([relationship]<> "Employee", "Included Dependent", "not Included Dependent")



Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!

Hi @amitchandak ,

 

Not only when the relationship is Employee then only Included Dependent.

For Example, when we selected the child A1 of Employee A0, and we want to see the dependent records, then we want it to return all the records of the Employee (A0) and the Employee's Dependents (A1,A2,A3). 

Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors