Calcualting majority status and keeping multiple filters on multiple columns

[Anonymous]

 Hi, 

I have below data and derived a specific data. Can you please help ?

Account NameÂ	FormualriesÂ	PlanÂ	Lives	StateÂ	Channel	Drug 1	Status
Fake AccountÂ	Aetna Value 5 Tier	a	100	WA	Commercial	Covered	Wrong AnswerÂ
Fake AccountÂ	Aetna Value 5 Tier	b	200	WA	Commercial	Covered	Wrong AnswerÂ
Fake AccountÂ	Aetna Value 5 Tier	c	300	WA	Commercial	Covered	Wrong AnswerÂ
Fake AccountÂ	Aetna Commercial Self Insured 5 Tier	d	400	WA	Commercial	Covered	Wrong AnswerÂ
Fake AccountÂ	Aetna Commercial Self Insured 5 Tier	e	500	WA	Commercial	Covered	Majority Commercial Status
Fake AccountÂ	Aetna Commercial Self Insured 5 Tier	f	600	WA	MedicareÂ	Covered	Wrong AnswerÂ
Fake AccountÂ	Aetna Commercial Self Insured 5 Tier	g	700	WA	MedicareÂ	Covered	Wrong AnswerÂ
Fake AccountÂ	Aetna Commercial Self Insured 5 Tier	h	800	WA	MedicareÂ	Covered	Majority Medicare  Status
Fake AccountÂ	Aetna Commercial Fully Insured 5 Tier	i	900	WA	MedicareÂ	Not Covered	Wrong AnswerÂ
Fake AccountÂ	Aetna Value Small Group 5 Tier	j	1000	WA	MedicareÂ	Not Covered	Wrong AnswerÂ
Fake AccountÂ	Aetna Value Plus Small Group 3 Tier NJ	k	1100	WA	MedicareÂ	Not Covered	Wrong AnswerÂ
Fake AccountÂ	Aetna Value Plus Small Group 4 Tier MD and WV	l	1200	WA	MedicareÂ	Not Covered	Wrong AnswerÂ
Fake AccountÂ	Aetna Value Plus Small Group 5 Tier	m	1300	WA	MedicareÂ	Not Covered	Majority Non medicare statusÂ
Fake 2 accountÂ	Aetna Value Plus Small Group 6 Tier	N	1400	AK	MedicareÂ	Not Covered	Wrong AnswerÂ
Fake 2 accountÂ	Aetna Value Plus Small Group 6 Tier	O	1500	AK	MedicareÂ	Not Covered	Wrong AnswerÂ
Fake 2 accountÂ	Aetna Value Plus Small Group 6 Tier	P	1600	AK	MedicareÂ	Not Covered	Wrong AnswerÂ
Fake 2 accountÂ	Aetna Value Plus Small Group 6 Tier	Q	1700	AK	MedicareÂ	Covered	Wrong AnswerÂ
Fake 2 accountÂ	Aetna Value Plus Small Group 6 Tier	R	1800	AK	MedicareÂ	Covered	Majority Medicare  Status
Fake 2 accountÂ	Aetna Value Plus Small Group 6 Tier	R	1900	WA	MedicareÂ	Covered	Majority Medicare  Status

Result :

ResultÂ
Fake AccountÂ	Aetna Commercial Self Insured 5 Tier	e	500	WA	Commercial	Covered	Majority Commercial Status
Fake AccountÂ	Aetna Commercial Self Insured 5 Tier	h	800	WA	MedicareÂ	Covered	Majority Medicare  Status
Fake AccountÂ	Aetna Value Plus Small Group 5 Tier	m	900	WA	MedicareÂ	Not Covered	Majority Non medicare statusÂ
Fake 2 accountÂ	Aetna Value Plus Small Group 6 Tier	R	1800	AK	MedicareÂ	Covered	Majority Medicare  Status
Fake 2 accountÂ	Aetna Value Plus Small Group 6 Tier	R	1900	WA	MedicareÂ	Covered	Majority Medicare  Status

[moumipanja · Accepted solution]

@Anonymous. What I understood from your clarification is that you want to return a table grouped by 4 columns - account, state, channel and cimiza with max of Lives column.

Does this table work for you?

This can be done as below:

1. Click on Edit Queries -> Select Group By -> Choose Advanced by clicking on the radio button -> Select your Group by columns  and add aggregation as the image below -> Click OK

Let me know if this works!

[v-yulgu-msft · Accepted solution]

Hi @Anonymous ,

Please add below measure to "Visual level filters" and specify its value to 1.

Maxlive =
IF (
    SUM ( Source_table[Lives] )
        = CALCULATE (
            MAX ( Source_table[Lives] ),
            ALLEXCEPT (
                Source_table,
                Source_table[Account Name ],
                Source_table[State ],
                Source_table[Channel],
                Source_table[Drug 1]
            )
        ),
    1,
    0
)

Best regards,

Yuliana Gu

[Anonymous]

Hi @v-yulgu-msft ,

If these colums are from differnt tables and then tables are connected in Power BI model.

How can i implement the DAX ?  As withALLEXCEPT i can use the columns of 1 table only.

Please help !

[moumipanja]

You can use advanced filtering to get the table you want without writing any single piece of code.

1. Select Table layout to display the original table.

2. Drag and drop column Status in the Visual level filters/ Page level filters/ Report level filters and then select Advance filtering.

[Note: you may choose your Filters depending on your requirement. I have used Page level filters here]

3. Set 'Show items when the value' as 'does not contain' and in the text box paste Wrong AnswerÂ. Click on apply filter.

[Anonymous]

Thank you @moumipanja for looking into. 

The data, i provided is fake and in real data each value is differerent. I am looking to calculating majority status at by computing max lives at account, state, channel and at product level.

Can you please help ! 

[moumipanja]

Hi, could you please elaborate a little more about your requirements? 

[Anonymous]

Hi,

I want to pull of majority lives for a each account,, state and channel and Cimiza level. In the below instance, for each unique account , state , channel and Cimiza - I want one row.

The result for the below set will be Two rows as State is differnt. I hope its helpful 

 RESULT 

Fake 2 accountÂ	Aetna Value Plus Small Group 6 Tier	R	1800	AK	MedicareÂ	Covered	Majority Medicare  Status
Fake 2 accountÂ	Aetna Value Plus Small Group 6 Tier	R	1900	WA	MedicareÂ	Covered	Majority Medicare  Status

Data 

Account NameÂ	FormualriesÂ	PlanÂ	Lives	StateÂ	Channel	CimizaÂ	Status
Fake 2 accountÂ	Aetna Value Plus Small Group 6 Tier	N	1400	AK	MedicareÂ	Not Covered	Wrong AnswerÂ
Fake 2 accountÂ	Aetna Value Plus Small Group 6 Tier	O	1500	AK	MedicareÂ	Not Covered	Wrong AnswerÂ
Fake 2 accountÂ	Aetna Value Plus Small Group 6 Tier	P	1600	AK	MedicareÂ	Not Covered	Wrong AnswerÂ
Fake 2 accountÂ	Aetna Value Plus Small Group 6 Tier	Q	1700	AK	MedicareÂ	Covered	Wrong AnswerÂ
Fake 2 accountÂ	Aetna Value Plus Small Group 6 Tier	R	1800	AK	MedicareÂ	Covered	Majority Medicare  Status
Fake 2 accountÂ	Aetna Value Plus Small Group 6 Tier	R	1900	WA	MedicareÂ	Covered	Majority Medicare  Status

[moumipanja · Accepted solution]

@Anonymous. What I understood from your clarification is that you want to return a table grouped by 4 columns - account, state, channel and cimiza with max of Lives column.

Does this table work for you?

This can be done as below:

1. Click on Edit Queries -> Select Group By -> Choose Advanced by clicking on the radio button -> Select your Group by columns  and add aggregation as the image below -> Click OK

Let me know if this works!