cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
WorkHard Helper II
Helper II

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 on a different Project.

Say I have:

 

Resource  ProjectNr  Desired Result

Tool1           01                      1

Tool1           01                      0

Tool2           02                      1

Tool3           03                      1

Tool1           04                      1

 

The desired result would be 4. 

This needs to be a DAX formula and it needs to be a column that I can reference in a table. 

 

 

 

The way I'd solve this in excel would be to create an "ID" out of the combination of Resource+ProjectNr Column and then only count the first occurrence: 

A                     B =COUNTIF($A$1:$A1,$A1)=1)+0

Tool101          1

Tool101          0

Tool202          1

Tool303          1

Tool104          1

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support
Community Support

Re: Calculate 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
)

first.PNG

 

 

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
Super User III
Super User III

Re: Calculate distinct value only if additional condition is true DAX formula

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 😂

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Check out my blog: Power BI em Português


Community Support
Community Support

Re: Calculate 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
)

first.PNG

 

 

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

WorkHard Helper II
Helper II

Re: Calculate distinct value only if additional condition is true DAX formula

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)

 

 

Super User III
Super User III

Re: Calculate distinct value only if additional condition is true DAX formula

Hi @WorkHard 

 

Did you tried the measure I have post before? 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Check out my blog: Power BI em Português


WorkHard Helper II
Helper II

Re: Calculate distinct value only if additional condition is true DAX formula

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.

 

Super User III
Super User III

Re: Calculate distinct value only if additional condition is true DAX formula

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.

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Check out my blog: Power BI em Português


Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors