Count number of IDs that do not have a subtype

[ghe7549]

I currently have 180 distanct facilities numbers(facilities) and i am trying to find out how many of those 180 ID's do not have a subtype.  Currently there are 437 different subtypes(subtypes).  These 2 tables are dimension table for the fact table(Assets).

The table names are in parentheses.  Facilities and subtypes and connected via their id in the asset table.  I am lost to how to figure this issue out any help would be wlecomed.

[ghe7549]

Below is one of the subtypes that i am trying to get a count for.  There are a total of 180 facilities but as you see there are only 11 that has this subtype. 

What is the best way to sum subtype column and then have a percentage based on the total number of facilities

Facility	Subtype	Count of Tag Number	Critical Infrastructure
Facility 1	Generator Quick Connect Cabinet	1	TRUE
Facility 2	Generator Quick Connect Cabinet	3	TRUE
Facility 3	Generator Quick Connect Cabinet	1	TRUE
Facility 4	Generator Quick Connect Cabinet	1	TRUE
Facility 5	Generator Quick Connect Cabinet	1	TRUE
Facility 6	Generator Quick Connect Cabinet	1	TRUE
Facility 7	Generator Quick Connect Cabinet	1	TRUE
Facility 8	Generator Quick Connect Cabinet	2	TRUE
Facility 9	Generator Quick Connect Cabinet	1	TRUE
Facility 10	Generator Quick Connect Cabinet	1	TRUE
Facility 11	Generator Quick Connect Cabinet	1	TRUE

[Ashish_Mathur]

Hi,

What exact result are you expecting?

[ghe7549]

At the end of the day i would like to have the number of facilities that do not have that subtype.  If there is a total of 200 facilities but 11 have the subtype i would like to have a card of something that shows 189 do not.

[v-shex-msft]

Hi @ghe7549,

How did these records store? Did those records that did not include the subtypes saving in your table with blank subtype field value or not existed in your table?
Notice: these two scenarios used different logic and expressions to calculate.

For the first one, you can calculate records with blank subtypes and use all records minus them to get the difference.

If you mean the second one, you need to get the list of subtype records and use except function to compare with all records and subtype records to get the difference.
Regards,

Xiaoxin Sheng

[v-shex-msft]

Hi @ghe7549,

Can you please share a pbix or some dummy data that keep raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[Ashish_Mathur]

Share some data to work with and also show the expected result.