Filter for NULL values on merged table with DAX

[Anonymous]

Hi all,

I am working from a database connected to PowerBI via Connect Live, therefore I am limited on the amount of data transformation I can do. I can only use measures, as columns and power query are disabled.

I have two tables (A and C), which are connected via a third table (B), and I would like to create a filter and count of an attribute in table C when an attribute in table A (Email) is NOT NULL.

TABLE A	TABLE B	TABLE C
First Name	Email (link to table A)	Unique ref. (link to table B)
Last Name	Building	SystemId
Email (link to table B)	Unique ref. (link to table C)	Software

In SQL, I have created a left join between the three tables, so that I have a list of all the email addresses in table A. Some of them correspond to a NULL SystemId in table C. I can see this easily if I create a table in PowerBI with the two columns Email and SystemId:

Email (table A)	SystemId (table C)
Example1	1234
Example2	[NULL]
Example3	2345

1. How can I get the count of NULL entries in C that correspond to Email addresses in table A (i.e. where email address in table A is NOT NULL)?
2. Also, how can I create a filter to the table in PowerBI so that the final users can filter themselves for a list that shows only rows for NULL or NOT NULL SystemId?

Many thanks in advance!

[v-yangliu-msft · Accepted solution]

Hi  @Anonymous  ,

According to your description, I have created three tables of ABC, which are left related to form the following table:

Here are the steps you can follow：

1. Get the count of NULL entries.

Create measure：
get the count of NULL entries =
CALCULATE(COUNTROWS('MergeA-B-C'),FILTER('MergeA-B-C','MergeA-B-C'[TableC.Systemid]=BLANK()))

Result：

2. list that shows only rows for NULL or NOT NULL SystemId

Create calculated column：

Column1 =
IF('MergeA-B-C'[TableC.Systemid]=BLANK(),"Null","Not Null")

Put the column1 into the slice to filter

Result：

You can downloaded PBIX file from here.

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.

[amitchandak]

@Anonymous , Try measure like

countx(values(Table[Email]), isblank(TableC[SystemId]))

or

countx(filter(values(Table[Email]), isblank(calculate(count(TableC[SystemId])))),[Email])

[Anonymous]

Thanks @amitchandak

I tried the two formulas. Unfortunately I get an error back as PowerBI says COUNTX() doesn't work with values of type Boolean (such as ISBLANK() ).

Also, in your formulas you use "Table", e.g. countx(values(Table[Email]), isblank(TableC[SystemId])). Do you mean "Table A"?

Thanks!

[amitchandak]

@Anonymous , Try countblank

Try a measure like

countblank(TableC[SystemID])

https://docs.microsoft.com/en-us/dax/countblank-function-dax

[Anonymous]

Hi @amitchandak,

The countblank on table C doesn't give me what I need because there are no NULL in table C.

When I do a left merge though, since I have more emails in table A than systemId in table C, some emails are not linked to any systemId and I need to get the count of the rows where SystemId is NULL (but email is not).

Any ideas??