Measure - number of dim keys that have no rows in fact table

[DmitryKo]

Simple setup:

• a fact table that has one row per value and a foreign key to dimension table
• a dimension table

I need a measure that calculates number of dimension keys that have no facts at all - e.g. either no rows in the fact table, or only rows with null values in the fact table.

Filters in DAX CALCULATE, unfortunately, only allow filtering based on column values, while here we need filting based on per-dimension aggregate.

EDIT: there seems to be no easy solution for such trivial task. Sad.

[v-yiruan-msft]

Hi @DmitryKo ,

If I understand correctly, you would like to get the count of dimension keys which have no facts at all(1. no rows in fact table; 2. the value of key is null in the fact table). If yes, you can create a measure as below to get it:

measures =
VAR _tab =
    CALCULATETABLE (
        VALUES ( 'facttable'[key] ),
        FILTER ( 'facttable', 'facttable'[value] = BLANK () )
    )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'dimensiontable'[key] ),
        FILTER (
            'dimensiontable',
            'dimensiontable'[key]
                IN _tab
                    || NOT ( 'dimensiontable'[key] IN VALUES ( 'facttable'[key] ) )
        )
    )

If the above one can't help you get the expected result, please provide some raw data in your fact table and dimension table (exclude sensitive data) with Text format and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following links to share the required info:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[DmitryKo]

Sample data set as small as this:

Keys

Alpha

Beta

Omega

Facts

Key	Value
Alpha	(null)
Beta	(null)
Beta	1

Result set:

Key	Measure result
Alpha	1
Beta	0
Omega	1

[v-yiruan-msft]

Hi @DmitryKo ,

You can create a measure as below to get it, please find the details in the attachment.

Measure =
VAR _selkey =
    SELECTEDVALUE ( 'Keys'[Key] )
VAR _tab1 =
    CALCULATETABLE (
        VALUES ( 'Keys'[Key] ),
        FILTER ( 'Keys', NOT ( 'Keys'[key] IN VALUES ( 'Facts'[key] ) ) )
    )
VAR _count =
    CALCULATE (
        DISTINCTCOUNT ( Facts[Value] ),
        FILTER ( 'Facts', 'Facts'[Key] = _selkey )
    )
VAR _countnull =
    CALCULATE (
        DISTINCTCOUNT ( Facts[Value] ),
        FILTER ( 'Facts', 'Facts'[Key] = _selkey && 'Facts'[Value] = BLANK () )
    )
RETURN
    IF ( _selkey IN _tab1 || _count = _countnull, 1, 0 )

Best Regards

[DmitryKo]

This would only work (correctly or not) when only one key is selected, due to this:

VAR _selkey =
    SELECTEDVALUE ( 'Keys'[Key] )

so it's useless in any real scenario that might include more than one key and/or aggregations over many other dimensions
EDIT: it produces incorrect results over sample dataset anyways

[v-yiruan-msft]

Hi @DmitryKo ,

How did you set the visuals in your report? Could you please share a simplified pbix file with me? You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[DmitryKo]

This code returns wrong result (false positives) and seems wrong anyways.

For example, "_tab" variable there would contain all keys that HAVE AT LEAST ONE row with nulls in the fact table, while we need to return keys that HAVE ONLY rows with nulls in the fact table. This is the part that causes false positives.

If we exclude it from the filter expression, we have empty result table over sample dataset, e.g. this part is wrong as well:

NOT ( 'dimensiontable'[key] IN VALUES ( 'facttable'[key] ) )

If specific fact is never present (as a row) in the fact table for a given key, wouldn't VALUES(facttable[key]) result in a empty list as well, rendering "IN" expression useless?