DAX - Max value from basepack

[venug]

Hi Everyone,

i have sample table like below one, i am going to calculate count on month. like O1B1 - 2, O1B2 - 1, O1B3 - 1, and O2B1 - 2, O2B2 - 2, O2B3 - 1.

from here i want to show max of count. B1 - 2, B2 - 2, B3 - 1.

I need DAX query of this scenario. 

Outlet	Basepack	Month
O1	B1	Jan
O1	B2	Jan
O1	B1	Feb
O1	B3	Mar
O2	B1	Jan
O2	B2	Jan
O2	B1	Feb
O2	B2	Feb
O2	B3	Feb

Output :

Basepack	Count
B1	2
B2	2
B3	1

Regards

Venu

[parry2k · Accepted solution]

@venug here is the measure for you

Measure 2 = MAXX ( SUMMARIZE('Table (2)', 'Table (2)'[Outlet],'Table (2)'[Basepack]), CALCULATE ( COUNTROWS ( 'Table (2)' ) ) )

drop BasePack and this new measure in a table visual and you will ge the result. Would appreciate Kudos 🙂 if my solution helped.

[venug]

@parry2k  thanks for soluiton,  it is working fine with different data.

[venug]

@parry2k  thanks a lot for providing solution...

i have got solution like below sanp shot.  almost i have got correct solution, need B3 max count as 1, but it's showing as 2.

can you  please help on this..

Regards

Venu

[amitchandak]

In summarize take a distinctcount basepake. You need to give that col a name. Use that to take out your final outcome.

Look at my example

CALCULATE(COUNTX(filter(SUMMARIZE(Sales,'Date'[date],"s1",SUM(Sales[Sales Amount])),[s1]>120),[s1])

You do not need filter here.

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601