Forum Discussion
Helper VCalculate distinct value only if additional condition is true DAX formula
Hi WorkHard ,
Try this:
1. Add an Index column in Power Query Editor.
2. Create a column.
First Instance = IF ( COUNTROWS ( FILTER ( 'Table', 'Table'[Resource] = EARLIER ( 'Table'[Resource] ) && 'Table'[ProjectNr] = EARLIER ( 'Table'[ProjectNr] ) && 'Table'[Index] <= EARLIER ( 'Table'[Index] ) ) ) = 1, 1, 0 )Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community SupportHi WorkHard ,
Try this:
1. Add an Index column in Power Query Editor.
2. Create a column.
First Instance =
IF (
COUNTROWS (
FILTER (
'Table',
'Table'[Resource] = EARLIER ( 'Table'[Resource] )
&& 'Table'[ProjectNr] = EARLIER ( 'Table'[ProjectNr] )
&& 'Table'[Index] <= EARLIER ( 'Table'[Index] )
)
) = 1,
1,
0
)
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
WorkHardThanks Icey,
This would work except I'm stuck down a rabbit hole trying to create an index column. I can't just use query editor to add an index column because I'm in Live mode that doesn't have a query.
Unsure how many more helper columns I need to create to get this to work. RANKX will rank the same number in my case which means I need to create another extra column as some sort of ID....
Update:
Thank you both for your help, I ended up using Icey's solution and created an index column using 2 additional columns:
First Column (RND) to create a random number for each row
Second Column (Index) to create an index number based on the random values
RND = RAND() Index = RANKX('TableName',[RND],[RND],ASC,Dense)- WorkHard
Mfelix,
Yes but unfortunately a measure wouldn't work in my situation. I'm summarizing a table and I'm creating a UNION with another table (cache old data tabke) vs (live data table) and this numbering needs to be a column, not a measure.
