cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
ppallavi Frequent Visitor
Frequent Visitor

Calcualting majority status and keeping multiple filters on multiple columns

 Hi, 

 

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

 

Account Name Formualries Plan LivesState ChannelDrug 1Status 
Fake Account Aetna Value 5 Tiera100WACommercialCoveredWrong Answer  
Fake Account Aetna Value 5 Tierb200WACommercialCoveredWrong Answer  
Fake Account Aetna Value 5 Tierc300WACommercialCoveredWrong Answer  
Fake Account Aetna Commercial Self Insured 5 Tierd400WACommercialCoveredWrong Answer  
Fake Account Aetna Commercial Self Insured 5 Tiere500WACommercialCoveredMajority Commercial Status 
Fake Account Aetna Commercial Self Insured 5 Tierf600WAMedicare CoveredWrong Answer  
Fake Account Aetna Commercial Self Insured 5 Tierg700WAMedicare CoveredWrong Answer  
Fake Account Aetna Commercial Self Insured 5 Tierh800WAMedicare CoveredMajority Medicare  Status 
Fake Account Aetna Commercial Fully Insured 5 Tieri900WAMedicare Not CoveredWrong Answer  
Fake Account Aetna Value Small Group 5 Tierj1000WAMedicare Not CoveredWrong Answer  
Fake Account Aetna Value Plus Small Group 3 Tier NJk1100WAMedicare Not CoveredWrong Answer  
Fake Account Aetna Value Plus Small Group 4 Tier MD and WVl1200WAMedicare Not CoveredWrong Answer  
Fake Account Aetna Value Plus Small Group 5 Tierm1300WAMedicare Not CoveredMajority Non medicare status  
Fake 2 account Aetna Value Plus Small Group 6 TierN1400AKMedicare Not CoveredWrong Answer  
Fake 2 account Aetna Value Plus Small Group 6 TierO1500AKMedicare Not CoveredWrong Answer  
Fake 2 account Aetna Value Plus Small Group 6 TierP1600AKMedicare Not CoveredWrong Answer  
Fake 2 account Aetna Value Plus Small Group 6 TierQ1700AKMedicare CoveredWrong Answer  
Fake 2 account Aetna Value Plus Small Group 6 TierR1800AKMedicare CoveredMajority Medicare  Status
Fake 2 account Aetna Value Plus Small Group 6 TierR1900WAMedicare CoveredMajority Medicare  Status

 

 

Result :

 

Result         
Fake Account Aetna Commercial Self Insured 5 Tiere500WACommercialCoveredMajority Commercial Status 
Fake Account Aetna Commercial Self Insured 5 Tierh800WAMedicare CoveredMajority Medicare  Status 
Fake Account Aetna Value Plus Small Group 5 Tierm900WAMedicare Not CoveredMajority Non medicare status  
Fake 2 account Aetna Value Plus Small Group 6 TierR1800AKMedicare CoveredMajority Medicare  Status
Fake 2 account Aetna Value Plus Small Group 6 TierR1900WAMedicare CoveredMajority Medicare  Status

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
moumipanja Regular Visitor
Regular Visitor

Re: Calculating majority status and keeping multiple filters on multiple columns

@ppallavi. 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?

 

1.JPG

 

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

 

1.JPG

 

Let me know if this works!

View solution in original post

Microsoft v-yulgu-msft
Microsoft

Re: Calcualting majority status and keeping multiple filters on multiple columns

Hi @ppallavi ,

 

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
)

1.PNG

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
moumipanja Regular Visitor
Regular Visitor

Re: Calcualting majority status and keeping multiple filters on multiple columns

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.

 

1.JPG1.JPG

ppallavi Frequent Visitor
Frequent Visitor

Re: Calcualting majority status and keeping multiple filters on multiple columns

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 Regular Visitor
Regular Visitor

Re: Calcualting majority status and keeping multiple filters on multiple columns

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

ppallavi Frequent Visitor
Frequent Visitor

Re: Calculating majority status and keeping multiple filters on multiple columns

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 TierR1800AKMedicare CoveredMajority Medicare  Status
Fake 2 account Aetna Value Plus Small Group 6 TierR1900WAMedicare CoveredMajority Medicare  Status

 

Data 

Account Name Formualries Plan LivesState ChannelCimiza Status
Fake 2 account Aetna Value Plus Small Group 6 TierN1400AKMedicare Not CoveredWrong Answer 
Fake 2 account Aetna Value Plus Small Group 6 TierO1500AKMedicare Not CoveredWrong Answer 
Fake 2 account Aetna Value Plus Small Group 6 TierP1600AKMedicare Not CoveredWrong Answer 
Fake 2 account Aetna Value Plus Small Group 6 TierQ1700AKMedicare CoveredWrong Answer 
Fake 2 account Aetna Value Plus Small Group 6 TierR1800AKMedicare CoveredMajority Medicare  Status
Fake 2 account Aetna Value Plus Small Group 6 TierR1900WAMedicare CoveredMajority Medicare  Status
        
moumipanja Regular Visitor
Regular Visitor

Re: Calculating majority status and keeping multiple filters on multiple columns

@ppallavi. 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?

 

1.JPG

 

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

 

1.JPG

 

Let me know if this works!

View solution in original post

Microsoft v-yulgu-msft
Microsoft

Re: Calcualting majority status and keeping multiple filters on multiple columns

Hi @ppallavi ,

 

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
)

1.PNG

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

ppallavi Frequent Visitor
Frequent Visitor

Re: Calcualting majority status and keeping multiple filters on multiple columns

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 !

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors