New Column with Blank value Count

[hello_MTC]

Hello there,

I have created a new table and added few column as seen in picture below.

I've also added a new column says "Counts"(DAX can be seen in picture). Currently it does count blank values from column "User_id" as '1'.

My concern is, I do no want to count balnk values available in column "User_id", it should be blank only.

What are the changes that i need to make in the dax functions seen in below picture? Also, If the column "Counts" extends more than number 9 than it should be return as '10+'. Just like table below.

"

Counts = var _countrows = CountTable[Incident_id]

return

COUNTROWS(

FILTER(ALL(CountTable),

_countrows = CountTable[Incident_id]

))"

[v-yiruan-msft · Accepted solution]

Hi @hello_MTC ,

You can update the formula of your calculated column [Counts] as below and check if it can return the correct result... Please find the details in the attachment.

Counts = 
VAR _count =
    CALCULATE (
        COUNT ( 'CountTable'[Incident_id] ),
        FILTER (
            'CountTable',
            'CountTable'[Incident_id] = EARLIER ( 'CountTable'[Incident_id] )
        )
    )
RETURN
    IF (
        ISBLANK ( _count ),
        BLANK (),
        IF ( _count <= 9, FORMAT ( _count, "General Number" ), "10+" )
    )

Best Regards

[v-yiruan-msft · Accepted solution]

Hi @hello_MTC ,

You can update the formula of your calculated column [Counts] as below and check if it can return the correct result... Please find the details in the attachment.

Counts = 
VAR _count =
    CALCULATE (
        COUNT ( 'CountTable'[Incident_id] ),
        FILTER (
            'CountTable',
            'CountTable'[Incident_id] = EARLIER ( 'CountTable'[Incident_id] )
        )
    )
RETURN
    IF (
        ISBLANK ( _count ),
        BLANK (),
        IF ( _count <= 9, FORMAT ( _count, "General Number" ), "10+" )
    )

Best Regards

[hello_MTC]

This is Perfect, but It is still not fixed. I need blank count if column user_id has balnk cells(Not on incident_id).

[v-yiruan-msft]

Hi @hello_MTC ,

You can update the formula of calculated column [] as below:

Counts =

VAR _count =

CALCULATE (

COUNT ( 'CountTable'[User_id] ),

FILTER (

'CountTable',

'CountTable'[Incident_id] = EARLIER ( 'CountTable'[Incident_id] )

)

)

RETURN

IF (

ISBLANK ( 'CountTable'[User_id] ),

BLANK (),

IF ( _count <= 9, FORMAT ( _count, "General Number" ), "10+" )

)

Best Regards

[hello_MTC]

Hi, You're awesome. But, I mistakenly mentioned that I need blank if user_id is blank. Actually I need 0 if user_is has balnk cell values.

I am sorry for the inconvinience!

[v-yiruan-msft · Accepted solution]

Hi @hello_MTC ,

Just update the formula of calculated column [Counts ]as below:

Counts =

VAR _count =

CALCULATE (

COUNT ( 'CountTable'[User_id] ),

FILTER (

'CountTable',

'CountTable'[Incident_id] = EARLIER ( 'CountTable'[Incident_id] )

)

)

RETURN

IF (

ISBLANK ( 'CountTable'[User_id] ),

0,

IF ( _count <= 9, FORMAT ( _count, "General Number" ), "10+" )

)

Best Regards

[hello_MTC]

Awesome, I put the 0 in quotation mark and it worked. Thank you so much.