Forum Discussion
Filter for NULL values on merged table with DAX
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 |
- 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)?
- 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!
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.
5 Replies
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
Super User
Anonymous , Try countblank
Try a measure like
countblank(TableC[SystemID])
https://docs.microsoft.com/en-us/dax/countblank-function-dax
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??
- amitchandak
Anonymous , Try measure like
countx(values(Table[Email]), isblank(TableC[SystemId]))
or
countx(filter(values(Table[Email]), isblank(calculate(count(TableC[SystemId])))),[Email])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!
