Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi All,
Need to calculate and return as 1 for the first occurrence of the value and 0 for the other occurrences as shown in the below table.
Telex No | Count |
43833.2502893519FR318601 | 1 |
43833.9684606481ER25725 | 1 |
44104.1667708333FR385998 | 1 |
44104.6828935185FR386164 | 1 |
44104.5247916667FR386122 | 1 |
44104.5247916667FR386122 | 0 |
44104.5247916667FR386122 | 0 |
44104.5247916667FR386122 | 0 |
44104.5247916667FR386122 | 0 |
44104.6828935185FR386164 | 0 |
44104.6828935185FR386164 | 0 |
44104.5247916667FR386122 | 0 |
44104.6828935185FR386164 | 0 |
44104.6830092593FR386171 | 1 |
44104.7237847222FR385734 | 1 |
44104.7615625FR386017 | 1 |
44104.6830092593FR386171 | 0 |
44104.7143518519FR386200 | 1 |
44104.7160300926FR386205 | 1 |
44104.7160300926FR386205 | 0 |
44104.7160300926FR386205 | 0 |
44104.7206134259FR385504 | 1 |
44104.7237847222FR385734 | 0 |
44104.6830092593FR386171 | 0 |
44104.7237847222FR385734 | 0 |
44104.7237847222FR385734 | 0 |
44104.6830092593FR386171 | 0 |
Thanks in advance for your assistance.
Regards
Sachin
@SSJ011 , first all you have add an index column
https://stackoverflow.com/questions/45715963/creating-an-index-column-for-power-bi
Then create a new column like this in dax
if([Index] = minx(filter(Table, [Telex No] = earlier([Telex No])),[Index]), 1, 0)
This is a job for Power Query, not for DAX. Please use the right tool for the job unless you want to suffer later on.
Hi, @SSJ011 , it requires indexing the table to differentiate rows; then you might apply this measure to a calculated column,
Count =
( Logs[Index]
= MINX (
FILTER ( Logs, Logs[Telex No] = EARLIER ( Logs[Telex No] ) ),
Logs[Index]
)
) + 0
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Can we acheive the same output using a "count" measure, instead of a calculated column?
@SSJ011 DAX can not identify which one is the first if only have one column as you show. you need other columns which can make each row is unique.
User | Count |
---|---|
47 | |
28 | |
23 | |
18 | |
15 |
User | Count |
---|---|
55 | |
34 | |
18 | |
17 | |
15 |