Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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] ) ) )
)
)
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
Proud to be a Super User!
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
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] ) ) )
)
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |