'DAX Measure to count Rows if relation may not exist

I am looking for a DAX measure to solve the following problem: Count the number of rows in the dimension table where the Fact table either has no rows or the score is 0.

Table A (Dimension Table)

ID	name
1	a
2	b
3	c

Table B (Fact Table)

ID	score
1	0
1	1
1	2
2	5

Expected Result In this example, I would expect 2, as ID=1 has one row with score=0 and ID=3 as no corresponding row in the Fact Table.

I came up with this measure which gives me the number of rows that have no corresponding row in the fact table, but I am not able to integrate the first condition: CALCULATE(COUNTROWS('Dimension'), FILTER ('Dimension', ISBLANK ( CALCULATE ( COUNT ('Fact'[id]) ) )))

powerbi dax

Solution 1:^[1]

Probably much more straightforward methods, but try this measure for now:

MyMeasure = 
VAR MyTable =
    ADDCOLUMNS(
        Table_A,
        "Not in Table_B", NOT (
            Table_A[ID]
                IN DISTINCT( Table_B[ID] )
        ),
        "Zero Score in Table_B",
            CALCULATE(
                COUNTROWS( Table_B ),
                Table_B[score] = 0
            ) > 0
    )
RETURN
    SUMX(
        MyTable,
        [Not in Table_B] + [Zero Score in Table_B]
    )

Solution 2:^[2]

You can also try this

CountID =
VAR ScoreZero =
    COUNTROWS ( FILTER ( TableB, [score] = 0 ) )
VAR NonExistentIDs =
    COUNTROWS ( EXCEPT ( DISTINCT ( TableA[ID] ), DISTINCT ( TableB[ID] ) ) )
RETURN
    ScoreZero + NonExistentIDs

Solution 3:^[3]

This also works, not sure it's a good idea to nest CALCULATE:

CALCULATE(COUNTROWS('Table_A'), FILTER ('Table_A', ISBLANK ( CALCULATE ( COUNT ('Table_B '[id]) ) ) || CALCULATE(COUNTAX(Filter('Table_B ','Table_B '[score]=0),'Table_B '[id])>=1)))

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	Jos Woolley
Solution 2	Angelo Canepa
Solution 3	Philipp Johannis

'DAX Measure to count Rows if relation may not exist

Solution 1:[1]

Solution 2:[2]

Solution 3:[3]

Sources

Related Questions

Solution 1:^[1]

Solution 2:^[2]

Solution 3:^[3]