Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I have two tables joined by AccountID. Relationship is one to many. I need to count the Number of NULL's in Table A that do not have TAG in Table B.
Im using Direct Query so ISBLANK does not Work.
NULL with No Tags = CALCULATE( COUNTROWS('Table A'),FILTER( 'Table A' ,'Table A'[STATUS]= BLANK() ) )
Should Return Count of 4. I see that it is counting all rows in Table A That are NULL. Should I add another Filer to see which AccountID's have TAGS?
Table A
AccountID | Status |
003fcb4e-a07b-4c30-8c13-c35f0a13c262 | NULL |
00705517-d96b-4647-a0da-67246178117f | NULL |
007de13c-f63e-4bbb-acb7-9309dde4ed7c | NULL |
008a14eb-9456-4902-b505-12c0632b6f97 | NULL |
00d32e84-c29d-4809-a657-ef2992471e19 | NULL |
00e86ef3-bba2-44eb-8a72-43424096d101 | NULL |
00fd2cb9-4209-4938-9657-b420798486a3 | NULL |
01063b28-d5ab-48ee-a9c6-7c8b99b995f6 | NULL |
0109ef64-f060-4434-a92c-d1672b66476c | NULL |
013f24d8-f194-46de-9e60-69ddf6df324e | NULL |
03025988-4ba6-4e6f-a8dc-d65db2f6293b | NULL |
03028869-5da9-4b59-8b8d-bd171d533235 | NULL |
0307f830-a3c7-438c-80a7-dc0ffae6cbca | NULL |
0362e71a-f366-40a4-9965-f51b95234e6d | NULL |
Table B
AccountID | Tag |
003fcb4e-a07b-4c30-8c13-c35f0a13c262 | Pre-Sale |
00705517-d96b-4647-a0da-67246178117f | Sold |
007de13c-f63e-4bbb-acb7-9309dde4ed7c | Pre-Sale |
008a14eb-9456-4902-b505-12c0632b6f97 | Deceased |
00d32e84-c29d-4809-a657-ef2992471e19 | Pre-Sale |
00e86ef3-bba2-44eb-8a72-43424096d101 | Pre-Sale |
00fd2cb9-4209-4938-9657-b420798486a3 | Pre-Sale |
01063b28-d5ab-48ee-a9c6-7c8b99b995f6 | Sold |
0109ef64-f060-4434-a92c-d1672b66476c | Bankruptcy |
013f24d8-f194-46de-9e60-69ddf6df324e | Deceased |
0149cd06-3da4-4610-8489-b5f1e6707b8d | Bankruptcy |
0156c9be-7370-4211-be71-9a12f218b86b | Sold |
01620cd3-96c2-40f6-8b4a-bedbb318fc7c | Bankruptcy |
01688074-2bc9-46c6-9e96-c2e7e1ade585 | Bankruptcy |
Seems like similar issue posted earlier, Could you please try the steps I've mentioned in the below thread...
https://community.powerbi.com/t5/Desktop/check-for-relation-with-other-table/m-p/524213#M245299
Proud to be a PBI Community Champion
Similar, But I need the COUNT of NULL ROWS from Table A that are not In Table B. What you linked gives me the total COUNT of Table B.
Thanks for your help.
Hi @ImaginedCloud,
Please try the formulas below.
Column = LOOKUPVALUE('TableB'[Tag],TableB[AccountID],'TableA'[AccountID]) Measure = CALCULATE(COUNTROWS('TableA'),FILTER('TableA','TableA'[Column]=BLANK()))
Then you could get your desired output.
Best Regards,
Cherry
User | Count |
---|---|
86 | |
82 | |
68 | |
67 | |
55 |
User | Count |
---|---|
123 | |
100 | |
90 | |
83 | |
66 |