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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Count number of times Measure appears in Columns

Hello,

 

I need help with counting the number of times a measure appears in multiple columns.
 

RBarao_94_0-1653302564526.png

 

In the image above you can see that for the Client 33, with various users, the most common number for MOD19 (19 Days in a Month) is 8,43. 
What I want to know is how can I count the number of times 8,43 appears in MOD19, MOD 20, MOD21 and MOD 22.
And then 8,01 in MOD19, MOD 20, MOD21 and MOD 22.

7,63 in.... ETC

The answer should be something like this p.e.:

no_PHCMOD19MOD20MOD21MOD22C_MOD19C_MOD20C_MOD21C_MOD22
338,438,017,637,2816162416

 

File PBI:

https://edenred-my.sharepoint.com/:u:/p/renato_santos/EWEbcjmgHcZOsGDV1ls04HsByVHhhLnJTuZu_sa2-75EPA...

 

 

Here's the excel file that i'm trying to reproduce in PBI.

File Xlsx (Right values):

https://edenred-my.sharepoint.com/:x:/p/renato_santos/Edp8EwXlz7pBq3k55ci9eLABSfAqZFeDulYUP1jzMh5bUg...

 

If it is easier in excel, i'll accept it. 😀

 

 

3 REPLIES 3
Icey
Community Support
Community Support

Hi @Anonymous ,

 

It is suggested to UnPivot the 4 "cen" columns like below:

Icey_0-1653543492998.png

Icey_1-1653543642989.png

 

Then create measures like so:

MOD =
MAXX (
    TOPN (
        1,
        ADDCOLUMNS (
            VALUES ( 'Face Value Test'[Value] ),
            "Moda", CALCULATE ( COUNT ( 'Face Value Test'[Value] ) )
        ),
        [Moda], 0
    ),
    'Face Value Test'[Value]
)
Count_MOD_3 =
VAR MOD_ =
    CALCULATE ( [MOD], ALL ( 'Face Value Test'[Value] ) )
VAR MaxMOD_ =
    IF ( MAX ( 'Face Value Test'[Value] ) = MOD_, MOD_ )
RETURN
    CALCULATE (
        COUNT ( 'Face Value Test'[Value] ),
        'Face Value Test'[Value] = MaxMOD_
    )
Filter Measure =
IF (
    MAX ( 'Face Value Test'[Value] )
        = CALCULATE ( [MOD], ALL ( 'Face Value Test'[Value] ) ),
    1
)

 

And you will get this:

Icey_2-1653546907845.png

 

Please check if the attachment is what you want.

 

 

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

Thank you very much for your help. But it not correct. 
I know what the right values are. 
I'll add the excel file that does what i want to do in PBI. 

Fowmy
Super User
Super User

@Anonymous 

Please share a sample or dummy PBI file with this information.
Save it in One Drive or Google Drive and share the link


Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

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