topic Count all other records excluding current record; consider blank values in DAX Commands and Tips

Count all other records excluding current record; consider blank values

akarasick1 — Tue, 14 Jul 2020 02:00:21 GMT

In the bottom visual below, you'll notice answers of "6", but I'm looking for an answer of "8". Below I describe the setup:

In my "Store" table, notice the empty values for Store Name

I am trying to count how many other stores there are when I look at one store name (middle visual). I'm trying to use the following measure:

Count of All Other Store =

var __selection = SELECTEDVALUE(Stores[Store Name])

var __table = FILTER(ALLSELECTED(Stores),Stores[Store Name]<>__selection)

return

COUNTROWS(__table)

Thanks!

AntrikshSharma — Tue, 14 Jul 2020 04:26:20 GMT

I am getting the correct count, Is there any other filter active on the report?

amitchandak — Tue, 14 Jul 2020 05:01:53 GMT

akarasick1 , do not use store name, Try to take store id and advantage of row context

calculate(count(Stores[Store_id]),filter(all(Stores), Stores[Store_id]<>max(Stores[Store_id])))

calculate(count(Stores[Store_id]),filter(all(Stores[Store_id]), Stores[Store_id]<>max(Stores[Store_id])))