Power BI Desktop Report Builder need help with measure to filter and show text.

[elkay73]

I need the report to show which Attendees are self medicating.   Jane Doe should say Self Med in the Count of PassID# column.

I can get the data to look correct until I try to add the text of 'Self Med'.  I must be missing something in the formula I'm using... 

Total Passes = if(Count(tblMEDPasses3[PassID#])=0,"Self Med",(Count(tblMEDPasses3[PassID#])))

When I use this it lists all of the Categories and ignores any filter I have on the page or visual.

Here is a link to my files.  One is the Excel file with the sample data and the other is the PBI report.

https://drive.google.com/drive/folders/1mAnCuyXtLJutmhdfq767eBv0S9lVTdSB?usp=sharing

Thank you for your time.

[Anonymous]

Hi @elkay73,

Thanks for reaching out to the Microsoft fabric community forum.

It looks like you're seeing unexpected behavior when trying to display "Self Med" in the Count of PassID# column is because DAX measures must return a consistent data type. Currently, your measure have numbers and text, which causes Power BI to treat the entire column as text. This can lead to issues like filters being ignored or the visual displaying all rows.

To achieve what you're trying to do, display the count of PassID# normally, but show "Self Med" when the count is zero.

You can use this approach : 

Total Passes (Text) =
VAR PassCount = CALCULATE(COUNT(tblMEDPasses3[PassID#]))
RETURN
IF(PassCount = 0, "Self Med", FORMAT(PassCount, "0"))

This version uses 'CALCULATE' to make sure it respects page and visual filters and 'FORMAT' to turn the count into text so that the entire measure returns a consistent text type.

I would also take a moment to thank @amitchandak, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.
Community Support Team

If this post helps then please mark it as a solution, so that other members find it more quickly.

Thank you.

[elkay73]

I was really hopeful as your description of why all rows were appearing makes sense.  Unfortunately, it is still showing all the rows.  I'm wondering if maybe it's due to the relationships I'm using?  I'll explore that next.

[elkay73 · Accepted solution]

I'm convinced this is a relationship issue.  Closing this and opening a new one requesting help with that.

[amitchandak]

@elkay73 , Try like

Total Passes = if(isblank(Count(tblMEDPasses3[PassID#])) || Count(tblMEDPasses3[PassID#])=0,"Self Med",(Count(tblMEDPasses3[PassID#])))

[elkay73]

Thank you for your reply.  I tried it and there was no change.  It's still showing all of the Program Names.  It should only be showing 2.