Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
gazzo1967
Helper III
Helper III

Showing a Zero if Calculate/Countrow has no record for user

HI All
I have an employee table relataionship modelled to a activity table by email.

I use the following to return my count 

GAZROLE_BA_TOTALCount1 = CALCULATE(
    COUNTROWS(ROLE_COUNT_IND),SEARCH("BA Wearer",ROLE_COUNT_IND[Role_BA],,0)&& SEARCH("Required standard achieved",ROLE_COUNT_IND[answer],,0))
 
Outcome
Role_IDUserNameLocationBA RoleemailGAZROLE_BA_TOTALCount1
1Mike.userTown BA Wearerblah11
2Dave.userCityBA Wearerblah21
3Joe.userCityBA Wearerblah32
8Paul.userTown BA Wearerblah51

This works fine 

But here is what I would like to do is show the users with a zero based upon my employee table, if they do not make the criteria for the count  not filter the visual to those who have only rows

My table example data

ROLE_COUNT_IND
Role_IDUserNameLocationBA_ Roleemailansweractivity_date
1Mike.userTown BA Wearerblah1Required Standard Achieved01/01/2023
2Dave.userCityBA Wearerblah2Required Standard Achieved03/02/2023
2Dave.userCityBA Wearerblah2Not Assessed04/03/2023
2Dave.userCityBA Wearerblah2Required Standard Achieved21/06/2021
3Joe.userCityBA Wearerblah3Required Standard Achieved27/12/2021
6Andy.userTown BA Wearerblah4Required Standard Achieved12/08/2021
3Joe.userCityBA Wearerblah2Required Standard Achieved20/07/2021
8Paul.userTown BA Wearerblah5Required Standard Achieved16/03/2023

Employee Data Table
Role_IDUserNameLocationemail
1Mike.userTown blah1
2Dave.userCityblah2
3Joe.userCityblah3
6Andy.userTown blah4
8Paul.userTown blah5
10Pete.userTown blah6
11Will.userCityblah7

If i use my Employee Data table values first

Role_IDUserNameLocationemail
 then add the count 

So the outcome becomes

Role_IDUserNameLocationemailGAZTESTBA1
1Mike.userTown blah11
2Dave.userCityblah21
3Joe.userCityblah32
6Andy.userTown blah40
8Paul.userTown blah51
10Pete.userTown blah60
11Will.userCityblah70

Thanks for any help in advance
Gary
1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

@gazzo1967 Try adding +0 to your measure.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

2 REPLIES 2
Greg_Deckler
Super User
Super User

@gazzo1967 Try adding +0 to your measure.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler 
You know what!!
I'm so annoyed with myself because I did that but put the  '+0' in the wrong place!!😡
I put it before the last bracket rather than after..
everydays a school day lol
Thanks Greg 🙂

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.