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
Anonymous
Not applicable

How to flag the maximum occurences by a category

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 :

QtnCrtn_0-1652792159505.png

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 
QtnCrtn_1-1652792386361.png

 

 

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 :
QtnCrtn_2-1652792541157.png

 

And finally I created the measure flg_max :

flg_max = IF([Nb_frm_avant]=[Maximum_category],"Yes","No")
Which results in : 
QtnCrtn_3-1652792597365.png

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.

QtnCrtn_4-1652792772573.png
 
 
I don't know if I'm clear enough. Suggestions would be greatly appreciated.
Best regards, 
Quentin.
7 REPLIES 7
tamerj1
Super User
Super User

Hi @Anonymous 

Beautiful code. Good job. 

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

Hi! Thank you!

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

@Anonymous 

Ok. Please try

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

Now I have this :

QtnCrtn_0-1652803155576.png

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 ? 

Create new measure

    CALCULATE (
        [Nb_frm_avant],
        ALLEXCEPT ( senso, senso[characteristic], senso[category] )
    )
Anonymous
Not applicable

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 :  

QtnCrtn_0-1652882126195.png

 

Any idea ?

@Anonymous 

What I meant like this

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

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