Forum Discussion

hello_MTC's avatar
hello_MTC
Helper III
4 years ago
Solved

New Column with Blank value Count

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]
))"

 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

  • Anonymous's avatar
    Anonymous
    4 years ago

    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

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      hello_MTC
      Helper III
      4 years ago

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        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