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

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.