Unique value on the table

[ChiRomeu]

Dear member,
 Please kindly help to calculate the unique value by the second column.

Best regards

Chi

[Shaurya]

Hi @ChiRomeu,

Use this formula to create flag for unique values:

UniqueMaster = IF(SUMX('Table',IF('Table'[MasterCode]=EARLIER('Table'[MasterCode]),1,0))=1,1,0)

Works for you? Mark this post as a solution if it does! 

[ChiRomeu]

hi Shaurya,
 thank you for your idea, but there is a problem which is mastercode 1s should also has an 1 and others 0.

 Is it possible?

Best regards

Chi

[Shaurya]

Hi @ChiRomeu,

Yes, it is possible. You want the UniqueMaster to be 1 if the original value is either unique or the value itself is 1. You can just add an OR condition to check that and get the desired result. 

Use this DAX Formula:

UniqueMaster = IF(SUMX('Table',IF('Table'[MasterCode]=EARLIER('Table'[MasterCode]),1,0))=1 || 'Table'[MasterCode]=1,1,0)

Result:

Works for you? Mark this post as a solution if it does!

[ChiRomeu]

Hi Shaura,

 I would like to get like the picture.

[Shaurya]

Hi @ChiRomeu,

I get it now. You want the first occurence of any value to have flag 1 and others as 0. The value may or may not repeat. Is that right?

[ChiRomeu]

hi Shaurya,

 it is possible to get that?

Best regards

Chi

[Shaurya · Accepted solution]

Hi @ChiRomeu,

Yes it is possible. However, there should be another column to identify unique records since the values may repeat in your MasterCode Column.

I've taken UniqueColumn for the sake of this example:

Here is the DAX Formula that you can use to get the UniqueMaster:

UniqueMaster = IF(RANKX(FILTER('Table','Table'[MasterCode]=EARLIER('Table'[MasterCode])),'Table'[UniqueColumn],,ASC,DENSE)=1,1,0)

This formula will:

1. Use the unique column to create ranks within the group of same values.
2. Assign flag value 1 to the first rank from every group and 0 to all others.

Here's how the rank will be created:

 

The DAX Formula mentioned above picks the first rank of every MasterCode. Note that for this to work, you must have a column with unique values.

Mark this post as a solution if that works for you!

[ChiRomeu]

Correct