Need help to create a DAX formula to count if at least one item is present

[JChris]

First of all, I'll give a glimpse of the structure:

Table: Assets (lists of assets)

Columns: Hostname, Serial Number, Location, Owner

1 .. *

Table: Softwares (list of all softwares installed on each and all of the assets)

Columns: Serial Number (of the Asset), Software Name, Vendor, ID (custom column)

* .. 1

Table: Software Status (list of unique softwares, using the ID from Softwares)

Columns: Software Name, Vendor, Status, Description, ID (from Softwares)

I want to count the number of assets that have at least one software marked as Status="BAD". How can I do that?

[vanessafvg · Accepted solution]

if i understand correctly this is what you can do

have you created a relationship between your tables i.e  asset.serialno ---- software.serialno i  and then sofware.id ---sofwarestatus.id

set one of the relationships to both directions ie between assset.serialno and sofware.serial no (take cognisense that this could have a negative effect elsewhere)

create a new measure after you have create a relationships,

bad sofware = calculate (countrows(assets), Status="BAD")

[JChris]

---

@vanessafvg wrote:

if i understand correctly this is what you can do

have you created a relationship between your tables i.e  asset.serialno ---- software.serialno i  and then sofware.id ---sofwarestatus.id

set one of the relationships to both directions ie between assset.serialno and sofware.serial no (take cognisense that this could have a negative effect elsewhere)

create a new measure after you have create a relationships,

bad sofware = calculate (countrows(assets), Status="BAD")

---

Amazing, I didn't even need the DAX formula. I was using some slicers on my page and now that the relationship is set to BOTH, when I click to filter using Software Status = "BAD" it will update my cards accordingly, and one of them is "Count of Hostname" 🙂