count ifs

[Anonymous]

I am trying to count the number of floors per building per facility.  I am using two measures to get this but it is not working

Facility Count = COUNTROWS('public facilities')

CALCULATE('Measure Table'[Facility Count],'public buildings'[id],'public floors'[id])

Any help would be greatly appreciated.

[Anonymous]

@AllisonKennedy   I created the measure count of floor ( COUNTA('public facilities'[name]) ).  I am still not geting the floors to total up.  If you look at the first one there is 2 garages with 6 floors each.  I am needing it to be North Garage 6 floors and West Garage 6 Floors.

[AllisonKennedy · Accepted solution]

@Anonymous  This is because you have [Floor] still in the table. A couple of things you can do, either use a matrix visual instead to get the subtotals;

[Count of Floors] in Values

[Facility] in Rows

[Building] in Rows

[Floor] in Rows

Expand down to all levels using the double connected arrows (looks like a fork kind of) at top of matrix.

or update your measure:

Count of All Floors = CALCULATE(COUNTA('public facilities'[name]) , ALL('public facilities'[floor]))

[AllisonKennedy]

@Anonymous  The countifs in DAX comes down to how you use it, if you want to count number of floors, simply create a measure: 

Count of Floors = COUNTA('public facilities'[name]) 

Then put this in a table or matrix visual. If using a matrix, put:

[Count of Floors] in Values

[Facility] in Rows

[Building] in Rows

If using a table, just put them in this order:

[Facility]

[Building]

[Count of Floors]

If you want distinct count, use @camargos88  formula. 

You can also use the COUNTROWS formula you have already created if you want to include blank values in your count.

Hope that makes sense?

[camargos88]

@Anonymous ,

Have you tried DISTINCTCOUNT('public facilities'[name]) ?