cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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

v-yulgu-msft Super Contributor
Super Contributor

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

v-yulgu-msft Super Contributor
Super Contributor

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
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 50 members 1,013 guests
Please welcome our newest community members: