Forum Discussion
Helper IHow 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
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:
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
7 Replies
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:
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
darentengmfsPost Prodigy
PaulDBrownCommunity Champion
Can you provide a sample dataset (or PBIX file) and a mock depiction of the expected output?
Thanks.
mdbuzzerI've added an expected outcome, so when an organisation has been with multiple sectors it should mark the column as 1
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.
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.
Please mark the post as a solution and provide a 👍 if my comment helped with solving your issue. Thanks!
- mdbuzzer
Anonymous 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 Anonymous 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
Please mark the post as a solution and provide a 👍 if my comment helped with solving your issue. Thanks!