Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
GuestUser
Helper V
Helper V

dax query - count of max occurence

Hi,

We are using power bi with ssas tabular model (connect live) option

we have below data:
Department    Hour    TransactionCount      week
D1                 1-2 pm            10                         week1
D1                  2-3 pm           20                         week1
D1                  3-4 pm          15                           week1
D1                 4-5 pm            20                            week1


Report needed in below format

Department      Max_Tran_Count     count_occurence         hour

D1                                    20                        2                          2-3 pm

need dax query for count_occurence

Any suggestion please

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

Perhaps:

 

count_occurence measure =
  VAR __Max = [Max_Tran_Count]
  VAr __Table =
    SUMMARIZE(
      'Table',
      [Department],
      [Hour],
      [week],
      "__TransactionCount",SUM([TransactionCount])
    )
RETURN
  COUNTROWS(FILTER(__Table,[__TransactionCount] = __Max))

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

7 REPLIES 7
Greg_Deckler
Super User
Super User

Perhaps:

 

count_occurence measure =
  VAR __Max = [Max_Tran_Count]
  VAr __Table =
    SUMMARIZE(
      'Table',
      [Department],
      [Hour],
      [week],
      "__TransactionCount",SUM([TransactionCount])
    )
RETURN
  COUNTROWS(FILTER(__Table,[__TransactionCount] = __Max))

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hi @Greg_Deckler

Thanks a lot ..it worked
(Just replaced sum by max)

Can u also pls suggest ..how to get hour column...since it is of string type

Great!

Do you mean you want to convert your hour column to numeric?

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hi @Greg_Deckler

Yes..sort of
As I have mentioned in above post..the report format that is needed..
Need to dispalay the hour also which has max transaction count

Oh, just use LOOKUPVALUE or MAXX(FILTER(...)...) for that. https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/td-p/985814


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hi @Greg_Deckler

I tried..but not able to get expected result Could you please help here in providing the formula

See attached PBIX:

 

count_occurence = 
    VAR __Max = MAXX(ALLSELECTED('Table'),'Table'[TransactionCount])
RETURN
    COUNTROWS(FILTER('Table',[TransactionCount] = __Max))

hour measure = 
    VAR __Max = MAXX(ALLSELECTED('Table'),'Table'[TransactionCount])
RETURN
    MINX(FILTER('Table',[TransactionCount] = __Max),[Hour])

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors