sum of distinct boolean values

[Brighton10]

Greetings everyone. I have an alarmStatus column that contains "ON"/"OFF" and would like to count the sum of distinct text when i filter using the country. For example Nigeria, Owo,Amos fashanu will have a countrows equals to 4 (datetime = 2021-02-02)when the alarmStatus is ON and Rabiu Azeez will have count of zero (0) because it is OFF. How can i write a DAX to calculate that? The last Column is for the DieselFlowOver40 Count. Thanks 

Desired Result

The data Tables

[parry2k]

@Brighton10 I just looked at the file and added the measure I provided previously, and I get the following result. What is the issue with it?

[Brighton10]

Thanks @parry2k , thats correct and i added a date filter to that measure as well. 

[parry2k]

@Brighton10 yes that will be very helpful, I'm not seeing the full picture.

[parry2k · Accepted solution]

@Brighton10 update measure and then check

Count On = 
CALCULATE ( COUNTROWS( Table ), 
KEEPFILTERS(Table[AlarmCode] = "DieselFlowOver40"), 
KEEPFILTERS(Table[AlarmStatus]  = "On" )
) + 0

[Brighton10]

Its still the same, Let me share my pbix file and have a look from there 

[Brighton10]

The file is under the DieselFlowOver40 folder https://drive.google.com/file/d/1cBlHHhmub1d9IEWWEG-mxmdHKika4gNb/view?usp=sharing 

[parry2k]

@Brighton10 you can change the measure as below and test it 

Count On = 
CALCULATE ( COUNTROWS( Table ), Table[AlarmCode] = "DieselFlowOver40", Table[AlarmStatus]  = "On" ) + 0

do test the measure in the visual and if it doesn't work, come back with details, explaining what is not working and what are the expectations.

Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Brighton10]

This measure works by counting the rows per country when its ON. When I include a region and Sitename filters its showing the sum value for all the sitenames. I want it to show on the specific sitename where it was ON, where it was OFF should be 0.

[parry2k]

@Brighton10 not sure fully understood but add this measure and use it in visual and see if that gets you what you are looking for

Count On = 
CALCULATE ( COUNTROWS( Table ), Table[AlarmStatus]  = "On" ) + 0

Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Brighton10]

Hi @parry2k  thanks for your response. So basically I want to create a calculated measure/column to show the distinct count when alarmCode=DieselFlowOver40 per day. So when the alarmStatus is OFF the count should always equal zero and when ON we count the number of rows.