dax query - count of max occurence

[GuestUser]

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

[Greg_Deckler · Accepted solution]

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))

[GuestUser]

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

[Greg_Deckler]

Great!

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

[GuestUser]

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

[Greg_Deckler]

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

[GuestUser]

Hi @Greg_Deckler

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

[Greg_Deckler]

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])