Count multiple rows for duplicate including blanks

[pardeepd84]

Hi,

First of all apologies if this is a duplicate request, I am trying to create a measure that will count multiple columns, if any of the rows are blank then it would return 0.  The columns are ID number, start date, start time - if any of these are blank then I would need it to return 0, also if any of the rows in all 3 column are all the same I need it count the number of entries it appears in the entire dataset.

Please see table below, the 4th column named results is what I want my measure to return.

ID          Start Date      Start Time   Results
1234                                                     0
            11/01/2021         15:30             0
9876    11/01/2021          09:05            1
9876    13/01/2021         13:45             1
3456    01/02/2021        19:30              2
3456    01/02/2021        19:30              2
6789    16/01/2021        19:30              3
6789    16/01/2021       19:30               3
6789    16/01/2021       19:30               3

[Jihwan_Kim · Accepted solution]

Hi, @pardeepd84 

Please try the below measure.

Result =
VAR currentid =
MAX ( 'Table'[ID] )
VAR currentdate =
MAX ( 'Table'[Start Date] )
VAR currenttime =
MAX ( 'Table'[Start Time] )
VAR newtable =
FILTER (
ALL ( 'Table' ),
'Table'[ID] <> BLANK ()
&& 'Table'[Start Date] <> BLANK ()
&& 'Table'[Start Time] <> BLANK ()
&& 'Table'[ID] = currentid
&& 'Table'[Start Date] = currentdate
&& 'Table'[Start Time] = currenttime
)
RETURN
COALESCE ( COUNTROWS ( newtable ), 0 )

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster.

[amitchandak]

@pardeepd84 , Try a measure like

countrows(Table)+0

or

countrows(Filter(allselected(Table),[ID] =max([ID])))

or

Count(Table[ID]) +0

or

CountX(Filter(allselected(Table),[ID] =max([ID])),Table[ID])