Table visualization not showing entries with blank measure in a column

[jwidauer]

Hi all,

I need your help with this weird issue I'm facing. I have a data model similar to the following:

Which is filled with this data:

And now I want to display the "Projects[Project ID]", "ProjectManagers[Name]" and "Count of Purchases[ID]" in a table.

So basically this:

Where 

count_purchases = COUNTROWS(Purchases)

But, as you can see, it doesn't show project 4, since it doesn't have any purchases and therefore "count_purchases" is a blank.

I already tried:

count_purchases = COUNTROWS(Purchases) + 0

Which then shows all combinations of "Projects[Project ID]" and "ProjectManager[Name]" and looks like this:

Which is, obviously, not what I want as well.

I also tried:

count_purchases =
IF( NOT(ISEMPTY(Purchases)) ||
    ISEMPTY(Projects);
    COUNTROWS(Purchases);
    0)

Which does give me the expected result of:

But when using this with my bigger model, with more data, takes to long to compute and uses so much RAM, that it doesn't actually display the table anymore.

So my question is:

How would I be able to display zeros instead of blanks in such a table?

Thanks a lot in advance!

[jthomson]

Try the easiest thing first - is there an option in your visual to show items with no data?

[jwidauer]

Unfortunately not that I know of.

I'm just using the standard Table visualization that comes with Power BI Desktop.

[jthomson]

Odd - in your values section, if you hit the drop down on Project ID I'd have expected an option to be there

[Anonymous]

WOW Thanks !!

[jwidauer]

Oh wow!
I totally missed that!

That's a lot better, but do you think it would be possible to show the blank as a "0"?

[jthomson]

---

wrote:

Oh wow!
I totally missed that!

That's a lot better, but do you think it would be possible to show the blank as a "0"?

---

Try that +0 workaround you referenced in your original post

[jwidauer]

That, unfortunately, again shows all combinations of "Projects[Project ID]" and "ProjectManager[Name]", like this:

[v-huizhn-msft]

Hi @jwidauer,

It's hard to calculate the measure based on rules. For example, which name should be displayed when the count_purchases is 0?

Best Regards,
Angelia

[jwidauer]

Hi @v-huizhn-msft,

So for me to be happy with the result, it would have to only show the combinations of "ProjectManagers[Name]" and "Project[ID]" where they have a relation e.g. the "Project[PM ID]" is equal to the "ProjectManagers[ID]".

From the measure standpoint, it should only display 0, if the "Project[ID]", "ProjectManagers[Name]" combination exists in the data.

I hope this clarifies a bit, what I want to do.

[v-huizhn-msft · Accepted solution]

Hi @jwidauer,

I test using your sample table, please create calculated column rather than measure in Project Table using the formulas below.

ProjectManagerName = RELATED(ProjectManagers[Name])

CountPurchase = CALCULATE(COUNT(Purchases[Purchase ID]),RELATEDTABLE(Purchases))+0

Then create a table visual to display it as follows.



Best Regards,
Angelia

[atleasttwochars]

Outputting 0 fixes it for the table visual, but not if you are doing calculations on "BLANK" fields. The table visual should NOT hide values with BLANK() (NULL) values.