Count of Categories in related table including uncategorized rows

[Arshadjehan]

I have two related tables with items linked on Code column.

Table1(Code, Description, Scope)    [1 side of the relationship]

Table2(Category, Code, Status)       [* side of the relationship]

There are Three types of Status : Completed, In Process, Rejected

I want a DAX measure or calculated column to count number of codes in table1 based on categories in table2. If an item is not assigned a category, it must be counted in a new category called "Unknown".

Regards

[Nathaniel_C]

Hi @Arshadjehan ,

Maybe you could dummy up some pictures in Excel to show what you have, and what you are looking for?

Please read this post to get your question answered more quickly:

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Nathaniel

[Arshadjehan]

Hi @Nathaniel_C 

Here is the dummy data and required output

Table 1
Code	Description	Scope
A123	xxx	abc
A124	yyy	abc
A125	zzz	wyx
A123	ppp	xyz
B123	xxx	abc
Table 2
Code	Category	Status
A123	Dummy Category	Completed
A123	Dummy Category2	Completed
A125	Dummy Category3	In Process
A124	Dummy Category	Rejected
Output
Status	Count of items
Completed	2
In process	1
Rejected	1
Unknown	1

Note that Unknown is not an explicit defined category, rather it is count of all codes in Table1 (1-side of relationship) which are not in Table 2 (*-side of the relationship)

Thanks

[v-chuncz-msft · Accepted solution]

@Arshadjehan 

You may use DAX below to add a calculated table.

Table =
UNION (
    SUMMARIZECOLUMNS (
        Table2[Status],
        "Count of items", DISTINCTCOUNT ( Table2[Code] )
    ),
    ROW (
        "Status", "Unknown",
        "Count of items", COUNTROWS ( EXCEPT ( VALUES ( Table1[Code] ), VALUES ( Table2[Code] ) ) )
    )
)