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.

MFelix's avatar
MFelix
Icon for Super User rankSuper User
6 years ago
Hi @ WorkHard,

Be aware that DAX measures are based on context so depending on the visualisation and columns or slicers filters you use you can get different results.

In this case you can do a measure (no need to have a column) similar to this

Count of tools = COUNTROWS( SUMMARIZETABLE(TABLE,Table[Project], "Tools", Distinct(Table[Tool]))

Made this measure by heart may need some adjustments. On cellphone not computer 😂