Unique scenario of creating a fiters with condition

[Anonymous]

Hi, 

I have a unique problem and would appreciate help on this.

I have two tables set up in my Power Bi and they are connected;

Table Plan
Plan name	Channel	Lives
A	Commercial	100
B	Medicare	600
C	Commercial	500
D	Commercial	400
E	Medicare	100
Table  Drug
1
2
3

mapping provided by the client -

	Channel
Drug 1	Commercial
Drug 2	Medicare	Commercial

I want to have a filter for the report called "Coverage". It will have two options, one Covered Accounts and other all. 

Result - 

When the user lands to this report, he/ she selects Drug 1 from the filter  -" Drugs" and select covered accounts from "Coverage" filter.  The coverage filter should refer to the mapping provided by the client. So, the filter should only pick up commercial plans and sum lives the commercial lives to give the result of 1000. If the user picks drug 2 and chose Covered accounts - then it should provide the sum of 1700 lives (adding both commercial and Medicare channels) 

If the user chooses "All" option from Coverage filter - Then it should not reference the mapping provided by the client and provide the total sum of 1700 for both the drugs  

Can you please help! 

Thanks,

Pallavi

[v-danhe-msft]

Hi @Anonymous ,

I could not understanding the 'map':

	Channel
Drug 1	Commercial
Drug 2	Medicare	Commercial

Is it a table and it seemed not complete? And could you mean to create a new table as slicer to filter the data you want? 

Regards,

Daniel He

[Anonymous]

Hi @v-danhe-msft ,

Sorry for not being clear. 

What i already have in the report-

Drugs filter 

Table Plan and Drug table are currently existing in my Power BI model. My client has provided me this new mapping table.  I wish to include this table in my data model and create a filter from this. To make it more easier to read, I have recreated the Map table 

Drug Name	Channel	Coverage
Drug 1	Commercial	c
Drug 1	Medicare
Drug 2	Commercial
Drug 2	Medicare	c

How I want it to work -

1. I want to have a filter for Coverage  with two options c and all 

2. When user picks Drug 1 and click on "c" , it should pick only Commercial plans for this drug 

3. If user picks Drugs 2 in the drug filter, and clicks "c", it should only pick Medicare plans for this drug 

4. If user picks Drugs 2 in the drug filter, and clicks "all", it should only pick all plans for this drug 

Please let me know,if  it  helps ! 

Thank you so much,

Pallavi