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
PowerBIOrg
Resolver II
Resolver II

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")

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
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.