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
mdbuzzer
Helper I
Helper I

How do I write a DAX to create a custom column for duplicate values?

Below is the DAX to get the total count of rows of an organisation when the distinct count of sector is greater than 1, meaning it counts the organisation when it has been with multiple sectors.

 

Multiple Sector Organisations =

COUNTROWS ( FILTER ( VALUES ( 'Inspection Scheduling Spreadsheet'[Licensed Establishment / Organisation] ),

CALCULATE ( DISTINCTCOUNT ( 'Inspection Scheduling Spreadsheet'[Sector]) > 1 ) ) )

 

How do I write a DAX to now create a column to put a '1' next to every organisation which has been with multiple sectors, so that I can then see a list of the organisations not just the total number on a card visual?

 

I'm looking for this result:

Organisation                  Sector           Duplicate Organisation 

London Hospital               AB                    1

London Hospital               RH                    1

Liverpool Hospital            AB                     0

Liverpool Hospital            AB                     0

 

 

Thanks in advance

1 ACCEPTED SOLUTION
v-eqin-msft
Community Support
Community Support

Hi @mdbuzzer ,

You could use the following formula to create a column :

Duplicate Organisation =
IF (
    CALCULATE (
        DISTINCTCOUNT ( Table1[Sector] ),
        ALLEXCEPT ( Table1, Table1[Organization] )
    ) > 1,
    1,
    0
)

My visualization looks like this:

9.23.1.1.PNG

Did I answer your question ? Please mark my reply as solution. Thank you very much.

If not, please upload some insensitive data samples and expected output.

 

Best Regards,

Eyelyn Qin

View solution in original post

7 REPLIES 7
v-eqin-msft
Community Support
Community Support

Hi @mdbuzzer ,

You could use the following formula to create a column :

Duplicate Organisation =
IF (
    CALCULATE (
        DISTINCTCOUNT ( Table1[Sector] ),
        ALLEXCEPT ( Table1, Table1[Organization] )
    ) > 1,
    1,
    0
)

My visualization looks like this:

9.23.1.1.PNG

Did I answer your question ? Please mark my reply as solution. Thank you very much.

If not, please upload some insensitive data samples and expected output.

 

Best Regards,

Eyelyn Qin

Sumanth_23
Memorable Member
Memorable Member

hi @mdbuzzer - if you require to display the name of organization which has been part of multiple sectors in a visual then you could add a column "Org Count" with value 1 for each record.

 

Duplicate Count.png

 

Add Organization name and "Org Count" column in a table visual and aggregate to sum or count.

This will display the Organization and the corresponding duplicates in a visual. 

 

Duplicate Count2.png

 

Please mark the post as a solution and provide a 👍 if my comment helped with solving your issue. Thanks!

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!

Proud to be a Super User!



@Sumanth_23 in your dataset, Org4 should return 0 because it has not been with different sectors, it should only return 1 if an organisation has been with e.g. A and B, if an organisation has two records both being A it should return 0. Only return 1 when it is different sectors but multiple times for the same organisation if that makes sense.

hi @mdbuzzer - The solution provided by @v-eqin-msft works perfectly - one watchout is that when you pull the calculated column into the report, you would require to select the option "Don't summarize" as seen below 

 

Sumanth_23_0-1600933172368.png

 

Please mark the post as a solution and provide a 👍 if my comment helped with solving your issue. Thanks!

 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!

Proud to be a Super User!



PaulDBrown
Community Champion
Community Champion

@mdbuzzer 

Can you provide a sample dataset (or PBIX file) and a mock depiction of the expected output?

Thanks.





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






I've added an expected outcome, so when an organisation has been with multiple sectors it should mark the column as 1

darentengmfs
Post Prodigy
Post Prodigy

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.