topic Re: How to flag the maximum occurences by a category in DAX Commands and Tips

How to flag the maximum occurences by a category

Anonymous — Tue, 17 May 2022 13:15:11 GMT

Hello, I have a problem with measures in DAX, I hope you can help me.

I would like to flag the cod_formula for which the characteristic doesn't occur most.

My dataset is like this :

I would like to have a 4th column with value "YES" if the characteristic is the most represented for each category and "NO" otherwise. In this example, it means there is only one value "NO" for the line

cod_formula 2021502 3 ; category CONSISTENCY ; characteristic 3/5 MEDIUM LOW

I tried to first calculate the number of occurence of each characteristic with the measure

Nb_frm_avant = CALCULATE(COUNT(senso[characteristic]),(FILTER(senso, ([InSocle] = "OUT" || [InSocle] = "NO CHANGE"))))

which is working correctly and returns

Then I calculate the maximum occurence for each characteristic with the measure :

Maximum_category = MAXX (

CALCULATETABLE (

VALUES ( 'senso'[characteristic] ),

ALLSELECTED ( senso[characteristic])

[Nb_frm_avant]

)

Which is also working correctly :

And finally I created the measure flg_max :

flg_max = IF([Nb_frm_avant]=[Maximum_category],"Yes","No")

Which results in :

Which is cool!

But I don't know how to have these same results adding the cod_formula beacause I have this table. I want the number above repeated for each duo category/characteristic and it's not the case as I only have 1 as a value.

I don't know if I'm clear enough. Suggestions would be greatly appreciated.

Best regards,

Quentin.

Re: How to flag the maximum occurences by a category

tamerj1 — Tue, 17 May 2022 13:50:44 GMT

Hi Anonymous

Beautiful code. Good job.

Maximum_category = MAXX ( CALCULATETABLE ( VALUES ( 'senso'[characteristic] ), ALLSELECTED ( senso[characteristic] ), REMOVEFILTERS ( senso[code_formula] ) ), [Nb_frm_avant] )

Re: How to flag the maximum occurences by a category

Anonymous — Tue, 17 May 2022 15:21:52 GMT

Hi! Thank you!

Unfortunately it doesn't solve my problem, I still have the same results in the last table I posted.

Re: How to flag the maximum occurences by a category

tamerj1 — Tue, 17 May 2022 15:47:38 GMT

Anonymous

Ok. Please try

Maximum_category = MAXX ( VALUES ( senso[characteristic] ), CALCULATE ( [Nb_frm_avant], ALLEXCEPT ( senso, senso[characteristic], senso[category] ) ) )

Re: How to flag the maximum occurences by a category

Anonymous — Tue, 17 May 2022 16:10:48 GMT

Now I have this :

In the column "Maximum_category" I now have the results I would like to have in "Nb_frm_avant" 😅

Moreover I think I need to do a modification in the measure Nb_frm_avant also but I don't see which one ?

Re: How to flag the maximum occurences by a category

tamerj1 — Tue, 17 May 2022 16:27:29 GMT

Create new measure

    CALCULATE (
        [Nb_frm_avant],
        ALLEXCEPT ( senso, senso[characteristic], senso[category] )
    )

Re: How to flag the maximum occurences by a category

Anonymous — Wed, 18 May 2022 13:57:33 GMT

Hello tamerj1 ,

Thank you for your help. I think I'm not far from the solution.

My last concern is about the Maximum_category measure.

I don't suceed to calculate the maximum of the measure you just told me in the last message (I called this measure Nb_occurence) by category

My measure is this one :

Maximum_category =

MAXX (

VALUES (senso[characteristic]),

CALCULATE (

[Nb_occurence],

ALLEXCEPT (senso, senso[characteristic], senso[category])

)

But it returns the same results as the measure as you can see :

Any idea ?

Re: How to flag the maximum occurences by a category

tamerj1 — Wed, 18 May 2022 16:48:51 GMT

Anonymous

What I meant like this

Maximum_category = CALCULATE ( MAXX ( VALUES ( senso[characteristic] ), [Nb_frm_avant] ), ALLEXCEPT ( senso, senso[characteristic], senso[category] ) )