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

[Oscar_Mtz_V]

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.

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

[Anonymous · Accepted solution]

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. 

[Oscar_Mtz_V]

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