Forum Discussion

PowerBIOrg's avatar
PowerBIOrg
Icon for Resolver II rankResolver II
4 years ago
Solved

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 P...
Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    4 years ago

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

     

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

     

    3.There're no relationships among tables.

     

    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.

     

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

     

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

     

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

    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.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi PowerBIOrg ,

 

One question, where is A4 in your input table?

 

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

You can filter the Paitent ID.

 

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.

 

 

  • PowerBIOrg's avatar
    PowerBIOrg
    Icon for Resolver II rankResolver II
    4 years ago

    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. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

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

       

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

       

      3.There're no relationships among tables.

       

      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.

       

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

       

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

       

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

      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.

      • PowerBIOrg's avatar
        PowerBIOrg
        Icon for Resolver II rankResolver II
        4 years ago

        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
 )