Forum Discussion

pardeepd84's avatar
pardeepd84
Helper III
5 years ago
Solved

Count multiple rows for duplicate including blanks

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 I...
Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

    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.

Jihwan_Kim's avatar
Jihwan_Kim
Super User
5 years ago

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.