Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Dear member,
Please kindly help to calculate the unique value by the second column.
Best regards
Chi
Solved! Go to 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:
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!
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!
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
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!
Hi Shaura,
I would like to get like the picture.
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?
hi Shaurya,
it is possible to get that?
Best regards
Chi
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:
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!
Correct
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
48 | |
26 | |
20 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
43 | |
19 | |
18 |