cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Oscar_Mtz_V Regular Visitor
Regular Visitor

Counting the number of occurences from the full table and show this number per row

Hi Everyone,

 

I would really appreciate anyone helps me with the following; I have the table bellow and I need to calculate the column in yellow.

Annotation 2020-01-22 140329.jpg

The column nees to evaluate, per row, how many rows of the same id are "null" (or any other condition).

 

For ID 1, every row shows 3 as three times the colour is blank.

For ID 2, every row shows 0 as the colour is never blank.

For ID 3, the only row shows 1 as one time the colour is blank.

 

In advance thanks for your support.

 

Oscar

 

1 ACCEPTED SOLUTION

Accepted Solutions

Re: Counting the number of occurences from the full table and show this number per row

Hello @Oscar_Mtz_V

The dax code needed to do this is as follows:

Calculation =
VAR Currentvalue = 'Table'[ID]
RETURN
    CALCULATE (
        COUNTROWS ( 'Table' );
        'Table'[ID] = Currentvalue;
        'Table'[Color] = BLANK ()
    ) + 0

 It wil calculated per row the amount of rows in the table that have the same ID and where the color value is blank.

 

Kind regards
Joren Venema

Data & Analytics Consultant
If this reply solved your question be sure to mark this post as the solution to help others find the answer more easily. 

View solution in original post

2 REPLIES 2

Re: Counting the number of occurences from the full table and show this number per row

Hello @Oscar_Mtz_V

The dax code needed to do this is as follows:

Calculation =
VAR Currentvalue = 'Table'[ID]
RETURN
    CALCULATE (
        COUNTROWS ( 'Table' );
        'Table'[ID] = Currentvalue;
        'Table'[Color] = BLANK ()
    ) + 0

 It wil calculated per row the amount of rows in the table that have the same ID and where the color value is blank.

 

Kind regards
Joren Venema

Data & Analytics Consultant
If this reply solved your question be sure to mark this post as the solution to help others find the answer more easily. 

View solution in original post

Oscar_Mtz_V Regular Visitor
Regular Visitor

Re: Counting the number of occurences from the full table and show this number per row

@Joren_venema you rock!!! Exactly what I needed, thanks!!

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors