Forum Discussion

WorkHard's avatar
WorkHard
Icon for Helper V rankHelper V
6 years ago
Solved

Calculate distinct value only if additional condition is true DAX formula

Hi, I'm trying to count the number of resources that are assigned to a Project. A simple distinct count wouldn't work because there are instances where a resource is assigned in the same period but...
Need Help
  • Icey's avatar
    Icey
    6 years ago

    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.

WorkHard's avatar
WorkHard
Icon for Helper V rankHelper V
6 years ago

Thanks 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)

 

 

MFelix's avatar
MFelix
Icon for Super User rankSuper User
6 years ago

Hi WorkHard 

 

Did you tried the measure I have post before? 

  • WorkHard's avatar
    WorkHard
    Icon for Helper V rankHelper V
    6 years ago

    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.

     

    • MFelix's avatar
      MFelix
      Icon for Super User rankSuper User
      6 years ago
      If your base is a table (independently of the way it's built) you can create a measure. You can even have measures based on temporary tables.

      If you can share more details or a mockup file I can try and help you achieve it.