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

Counting Duplicate Values In A Column

Dear Community, 

 

Hope my message finds you well!

 

I need to count duplicate values in a report with a measure. So far I was able to create a calculated column with the following code:

 

Atanas_Atanasov_0-1658127290210.png

But I'd like to have it in a measure. 

 

Can you please help me translate the DAX code from the column into a measure?

 

Thank you so much for your help,

 

Atanas

2 ACCEPTED SOLUTIONS
v-jianboli-msft
Community Support
Community Support

Hi @Atanas_Atanasov ,

 

Based on your description, I have create a simple sample:

vjianbolimsft_0-1658306812009.png

 

Here is the DAX:

 

Number of Opportunities =

IF (

    MAX ( 'RAWData'[Hybird IT Service] ) + MAX ( 'RAWData'[A&PS] ) = 0,

    BLANK(),

    COUNTX (

        FILTER (

            ALL ( 'RAWData' ),

            [Hybird Opportunity Id] = MAX ( 'RAWData'[Hybird Opportunity Id] )

        ),

        [Hybird Opportunity Id]

    )

)

 

Then choose show items with no data:

vjianbolimsft_2-1658307055030.png

 

Output:

vjianbolimsft_1-1658306812011.png

 

If the result is not what you want, please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

Refer to:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

 

Best Regards,

Jianbo Li

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

View solution in original post

Thank you for your help!

View solution in original post

4 REPLIES 4
v-jianboli-msft
Community Support
Community Support

Hi @Atanas_Atanasov ,

 

Based on your description, I have create a simple sample:

vjianbolimsft_0-1658306812009.png

 

Here is the DAX:

 

Number of Opportunities =

IF (

    MAX ( 'RAWData'[Hybird IT Service] ) + MAX ( 'RAWData'[A&PS] ) = 0,

    BLANK(),

    COUNTX (

        FILTER (

            ALL ( 'RAWData' ),

            [Hybird Opportunity Id] = MAX ( 'RAWData'[Hybird Opportunity Id] )

        ),

        [Hybird Opportunity Id]

    )

)

 

Then choose show items with no data:

vjianbolimsft_2-1658307055030.png

 

Output:

vjianbolimsft_1-1658306812011.png

 

If the result is not what you want, please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

Refer to:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

 

Best Regards,

Jianbo Li

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

Thank you for your help!

amitchandak
Super User
Super User

@Atanas_Atanasov , Based on what I got

a new column

Countx(Filter(Table, [Opportunity ID] = earlier([Opportunity ID]) ), [Opportunity ID])

@amitchandak - thank you for your swift reply, but when I enter the argument of Earlier I am not allowed to type any column name (please note I'd like to create a measure, not calculated column)

Atanas_Atanasov_0-1658129116952.png

All due respect, 

 

Atanas

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.