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 value...
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

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

    • hello_MTC's avatar
      hello_MTC
      Helper III
      4 years ago

      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!

      • Anonymous's avatar
        Anonymous
        Not applicable
        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