Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have a measure which displays blank value instead of 0.
My problem scenario is like this.
Ihave different tables with relations, in which one table acts like fact table and all other tables are dimension tables.
Suppose Table ITEM (fact) has fields ItemID,statusid, amount,dateid fields And Status(Dimension) table has statusid, status code fields and Calendar(Dimension) table has dateid,date
Table ITEM Joined with STATUS based on the statusid field
And ITEM joined with calendar based dateid field
Now I created 2 measures in Table ITEM based on the below formula:
Open Count:=CALCULATE(COUNT('Item'[ID]), FILTER(STATUS,status[Status Code]="open"))
Complete Count:=CALCULATE(COUNT('Item'[ID]), FILTER(STATUS,status[Status Code]="complete"))
For the period from August5th-10th there are no records in ITEM table with status open, but ITEM has 4 records with status complete.
So measure Open Count should display count value as 0 and measure Complete should display as 1 for four records.
But the output displayed as below in which Open count i wanted to represent as 0
Please help me how to represent 0 instead of blank in the column Open Count
Solved! Go to Solution.
here is link to another post in which I provided the solution to show "NA" if measure is blank, you can use the approach to show 0.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Hi,
Try this
=IF(ISBLANK(CALCULATE(COUNT('Item'[ID]), FILTER(STATUS,status[Status Code]="open"))),0,CALCULATE(COUNT('Item'[ID]), FILTER(STATUS,status[Status Code]="open")))
Does this work?
Hi,
Try this
=IF(ISBLANK(CALCULATE(COUNT('Item'[ID]), FILTER(STATUS,status[Status Code]="open"))),0,CALCULATE(COUNT('Item'[ID]), FILTER(STATUS,status[Status Code]="open")))
Does this work?
Hi,
I think the issue here is that it is not displaying Open count rows..
For that, I suggest you can edit the table view and for Status id column in the table (if it exists), you can choose 'Show items with no data'
Sorry, I am not sure how to represent blank as '0' if you want it specifically that way..
However, you can easily count the no. of blanks or zeros in table using a SUMX() function
Hope this helps
here is link to another post in which I provided the solution to show "NA" if measure is blank, you can use the approach to show 0.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
have you try a calculated column on your fact item table?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
104 | |
77 | |
67 | |
63 |
User | Count |
---|---|
144 | |
107 | |
105 | |
82 | |
69 |