SQL to DAX

[Anonymous]

I want to count the rows where 'bedrag' is null

F.Bedrag
, M.Monster_NameDatum
, M.Monster_facturatiecode
, M.Monster_Naam
FROM DimMonster M
LEFT JOIN FactFacturering F on m.SK_MonsterKey = F.FK_MonsterKey
WHERE F.Bedrag is null

how do i count this in dax? Do i need to put a specific relationship in the the dax code?

[v-frfei-msft]

Hi @Anonymous ,

Have a try using this formula.

cal =
VAR a =
    VALUES ( M[SK_Monsterkey] )
RETURN
    CALCULATE (
        COUNTROWS ( F ),
        FILTER ( F, F[bedrag] = BLANK () && F[FK_MonsterKey] IN a )
    )

[Anonymous]

Thanks for your reply when i try your query it says 'Empty'
i actually want to count DimMonster so i changed the countrows to monster is this also correct?

[Anonymous]

Hello @Anonymous ,

You can try below formula:

Column = CALCULATE(COUNTROWS(FactFacturering),bedrag=BLANK()).

Also, create relationship between DimMonster and FactFacturering.

Thanks,

Bhavankit

[Anonymous]

I want the 8792 also to show in the card but it doenst filter. I tried both suggestions above did not work

[AnthonyTilley]

This would depend on your report so we would need more detail as to what you are trying to achive.

that beeing said you can create a relationship between the two tables on your monsterkey filed and then create a simple measure for 

measure = count( FactFacturering[Bedrag])

alternativly if you do not want to create the relationship, then use a calulcalated colunm to look up the bedrag filed and add it to your Dimmonster table

Colunm = LOOKUPVALUE( FactFacturering[Bedrag],  DimMonster[SK_MonsterKey], FactFacturering[FK_MonsterKey])

Then use a count against this new field.

adding the relationship would always be the prefered approach.

[AnthonyTilley]

apologise misred your inital post the count i provided would only give you how many are not blank.

to count count null values use countblank( FactFacturering[Bedrag])

[Anonymous]

I want to count the empty column in gefactureerd. It already has an relationship

[AnthonyTilley]

Does countblank(table[gefactureerd]) not work