ALLEXCEPT not working when filtering blanks

Question

I have a simple problem. My DAX measure does not seem to be working correctly when I filter for non-existing values. Here are some details:

Table: Column1: A,A,A,A,A,B,B,B,B

Column2: 1,2,3,4,5,1,2,3,5

Measure = calculate(countrows(table), allexcept(column1))

Card Visual returns correct row count when I filter by column1 (any value in filtering pane)

However it returns wrong row count when I filter by column2 = "4" and Column1 = "B" (in filtering pane). It seems that it should ingore filtering by column2 and it does except when I specifically filer for value = "4". It gives "blank" result value in a card visual then.

Any ideas why?

Here's the screen. I would like to populate that blank cell with "4" (in a singe-table data model.enter image description here

Answer 1

In your case you dont need to add allexcept in your measure. Below code would be fine.

TestMeasure = countrows(Test_Data)

PFB screenshot

Answer 2

I am hoping that you have a data model as following

table name _dim1

colA
A
B
C

table name _dim2

colB
1
2
3
4
5

table name _fact

colA	colB
A	1
A	2
A	3
A	4
A	5
B	1
B	2
B	3
B	5
C	2
C	3

If you have this you can reach where you need by using following measures

Measure3 =
CALCULATE ( COUNTROWS ( _fact ), ALL ( _dim2[colB] ), VALUES ( _fact[colA] ) )

Measure9 =
VAR _1 =
    MAX ( _dim2[colB] )
VAR _2 =
    CALCULATE (
        MAXX (
            FILTER ( _dim2, _dim2[colB] <= _1 ),
            LASTNONBLANKVALUE ( _dim2[colB], [Measure3] )
        ),
        ALL ( _dim2[colB] )
    )
RETURN
    _2
    
Measure10 =
VAR _1 =
    MAX ( _dim2[colB] )
VAR _2 =
    CALCULATE (
        MAXX (
            FILTER ( _dim2, _dim2[colB] > _1 ),
            FIRSTNONBLANKVALUE ( _dim2[colB], [Measure3] )
        ),
        ALL ( _dim2[colB] )
    )
RETURN
    IF ( ISBLANK ( [Measure9] ) = TRUE (), _2, [Measure9] )

I don't think you can reach here from a single table like following

colA	colB
A	1
A	2
A	3
A	4
A	5
B	1
B	2
B	3
B	5
C	2
C	3