DAX Formula - Extra 0

[Net_Sec_Alt]

Hello all,

Why am I getting this blank row with a 0 (see the whitespace and arrow) in my table visual when I use the below DAX?

If I change the 0s on row 18 and 20 of the code to BLANK () I can get it to go away, but I need the 0s to show in the other rows of the chart so that is no good.

I am a total DAX noob who can't figure out why I am getting a blank row in this table if I use NOT(ISBLANK... what am I missing?

FWAL TOTAL =

IF(

    NOT(ISBLANK(SELECTEDVALUE('Raw Data'[Account Number]))),

    IF(

        COUNTROWS(

            FILTER(

                'FWAL',

                'FWAL'[Account Number] = SELECTEDVALUE('Raw Data'[Account Number])

            )

        ) > 0,

        SUMX(

            FILTER(

                'FWAL',

                'FWAL'[Account Number] = SELECTEDVALUE('Raw Data'[Account Number])

            ),

            'FWAL'[4FWAL Total]

        ),

        0

    ),

    0

)

[Anonymous · Accepted solution]

Hi  @Net_Sec_Alt ,

The Total value is not shown under the other data, I think it's because these columns are all calculated columns and have the aggregation status removed.

However, measure automatically generates a total based on your function, which follows the context of the "Total" row and is calculated in that context. Therefore, a measure used in the columns of a table visualization may have unexpected values in the "Total" column. However, the Total value can be calculated according to your own custom rules.

If you want other rows to show 0, but there is no option to show 0 in the aggregation operation, you can use the create a measure, IF()+HASONEVALUE() function to determine if it is a Total, if it is, it can be calculated according to your own needs, such as the average, Sum, etc., and if it is not a Total, then it will show the normal value.

Index_Measure =
IF(
    HASONEVALUE('Raw Data'[Account Number]),SUM('FWAL'[Index]),0)

[FWAL TOTAL] can be changed to blank() according to your formula.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly