Forum Discussion

PowerBIOrg's avatar
PowerBIOrg
Resolver 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 PatientID and whether or not I check the "Included Dependent".

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. 

 

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.

6 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

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

     

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

    • PowerBIOrg's avatar
      PowerBIOrg
      Resolver II
      4 years ago

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

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