find associated value of a filter

[Anonymous]

CLAIM_NO             PROCEDURE_CODE         ORGANIZATION              STAFF NAME

       1                                  999                               A                              MICHAEL

       2                                  121                               A                              SAM

       3                                  999                               A                              MICHAEL

       4                                  122                               A                              MICHAEL

       5                                  999                               B                              KELLY

       6                                  999                               B                              KELLY

       7                                  999                               B                              KELLY

       8                                  122                               B                              KRIS

       9                                  121                               B                              KRIS

      10                                 999                               C                              SAM

      11                                 121                               C                              SAM

I have STAFF NAME as a drop down filter. Here is what I am trying to achieve : When a staff member is selected , the total count of each PROCEDURE_CODE in the the organization is calculated.

Example1: if MICHAEL is selected then the total number of each procedure is calculated : 999 ---> 2 121 ---> 1 122 ---> 1

Example2: if KELLY is selected then the total number of each procedure is calculated : 999 ---> 3 121 ---> 1 122 ---> 1

NOTE: There is NOfilter for ORGANIZATION or PROCEDURE_CODE 

The difficulty that I am facing right now is how to lookup/find the ORGANIZATION name of the selected STAFF NAME from the table and then how to use that value to find the total per procedure code within the organization. Any idea how that can be done?

[parry2k]

@Anonymous  Not sure which visual you are using but if you use matrix visual, you can throw procedure on rows and organization in columns and value will be count and you will get what you are looking for without doing anything special.

[Anonymous]

Thank you @parry2k  for the reply. The graph I am using is bar chart to show the distribution per procedure code. I do not wanna use the matrix although it is giving me the solution because my end user requested a bar chart. So the trick is to :

For the selected user find the organization he/she associated with. Then find the distribution of all procedure codes within that Organization(and ignore the user name/staff member filter) and display them in a bar chart..but i do not know how to do that.

[Anonymous]

CLAIM_NO             PROCEDURE_CODE         ORGANIZATION              STAFF NAME

       1                                  999                               A                              MICHAEL

       2                                  121                               A                              SAM

       3                                  999                               A                              MICHAEL

       4                                  122                               A                              MICHAEL

       5                                  999                               B                              KELLY

       6                                  999                               B                              KELLY

       7                                  999                               B                              KELLY

       8                                  122                               B                              KRIS

       9                                  121                               B                              KRIS

      10                                 999                               C                              SAM

      11                                 121                               C                              SAM

I have STAFF NAME as a drop down filter. Here is what I am trying to achieve : When a staff member is selected , the total count of each PROCEDURE_CODE in the the organization is calculated.

Example1: if MICHAEL is selected then the total number of each procedure is calculated : 999 ---> 2 121 ---> 1 122 ---> 1

Example2: if KELLY is selected then the total number of each procedure is calculated : 999 ---> 3 121 ---> 1 122 ---> 1

NOTE: There is NOfilter for ORGANIZATION or PROCEDURE_CODE 

The difficulty that I am facing right now is how to lookup/find the ORGANIZATION name of the selected STAFF NAME from the table and then how to use that value to find the total per procedure code within the organization. Any idea how that can be done?

[parry2k]

@Anonymous you can surely use bar chart, you put Procedure on X Axis and Organziation on Legend.

[Anonymous]

but here is the problem:

1- Staff member filter is not linked to the organization bar graph to filter the results. This is because I am enforcing single selection of the staff member filter by using ChicLetSlicer . If I allow the filter to also filter the organization bar chart then the distribution of that staff member only will be shown in the graph which is something i do not want. I want the distribution in the whole organization for all staff members.

2- But also because the filter is not linked to the graph, all organizations are displayed in the bar graph.

[parry2k]

@Anonymous  my mistake, I should have asked to share how your tables are connected. I just answered based on your initial question and not sure what and how tbales are connected? Can you share that information?

Following link explain the best practice to get your answere quickly 

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Anonymous]

my apology for not being clear. when I say connected I meant the filter interact with the bar chart. In other words if you select a staff member the bar chart will be filtered

[Anonymous]

@parry2k  fair enough 🙂

The data that I have is just one table as I mentioned in the original post.

I attached two snapshot of the view.. one where I did not link the filter to the bar chart and you can see clearly it shows ALL orgnizations.

In the second snapshot, I did link the two but it does NOT show the distribution in the orgnization but rather it shows the distribution for that specific staff member/provider which as you can see identical to the provider bar chart.

The measures in Organization bar chart graph that shows the distributions per procedure code are :

DIVIDE(CALCULATE(

    COUNT(vwBI_CodingStudy_v4[ClaimServiceId]),

    FILTER(

        vwBI_CodingStudy_v4,

vwBI_CodingStudy_v4[PROC_CODE] = "999"

    )

) , COUNT(vwBI_CodingStudy_v4[ClaimServiceId]))

DIVIDE(CALCULATE(

    COUNT(vwBI_CodingStudy_v4[ClaimServiceId]),

    FILTER(

        vwBI_CodingStudy_v4,

vwBI_CodingStudy_v4[PROC_CODE] = "121"

    )

) , COUNT(vwBI_CodingStudy_v4[ClaimServiceId]))

DIVIDE(CALCULATE(

    COUNT(vwBI_CodingStudy_v4[ClaimServiceId]),

    FILTER(

        vwBI_CodingStudy_v4,

vwBI_CodingStudy_v4[PROC_CODE] = "222"

    )

) , COUNT(vwBI_CodingStudy_v4[ClaimServiceId]))

[Anonymous]