How to remove blank row when using a measure to display count of rows in related tables

[Anonymous]

Hi All,

I have a very simple model comprising of the following tables.

Model

This is the classic many-many relationship.  1 Author can have many Titles and vice-versa.

Authors

ID	Author
100	John Doe
200	Jane Doe
300	Adam Smith
400	William Shakespeare

Titles

ID	Title
10	Julius Caesar
20	The merchant of Venice
30	Some book 1
40	Some book 2
50	Wealth of Nations
60	Romeo Juliet

AuthorTitles

AuthorID	TitleID
400	10
400	20
300	50
400	60

MeasureCount = 
var actual_count=CALCULATE(COUNTROWS(RELATEDTABLE(Titles)))
return IF(ISBLANK(actual_count),0,actual_count )

How do I remove the blank row?

Thanks,

Sau

[parry2k]

@Anonymous, although you have a solution, here is the updated measure

Count = COUNTROWS ( AuthorTitle ) + IF ( NOT ISBLANK ( MAX ( Authors[Author] ) ), 0 )

[Anonymous]

Hello @Anonymous ,

Total Title =

VAR TotalTitle = COUNTROWS(AuthorTtiles)

RETURN

IF( ISBLANK( TotalTitle ), 0, TotalTitle )

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Anonymous]

Hello @Ashish_Mathur ,

Thanks so much for looking into this.

Problem 1

I downloaded you PBI.  This is what  I see.

I can see that you have constructed a Table visual using the Titles table and then added the custom Measure.

This eliminates the blank row. But, it does not show me the authors with 0 titles. (Jane Doe and John Doe)

Problem 2

I tried your DAX expression by creating a DAX expression in the Authors table

The blank row has come up again.

Looking at your response, you appear to have fixed this. What did you do and what did I miss?

Regards,

Sau

[Ashish_Mathur]

Hi,

I am confused by your reply.  Have i answere your question?

[Anonymous]

Hello @Ashish_Mathur ,

Thanks so much for looking into this problem.  As mentioned by @CNENFRNL  , the real problem was with the cardinality of the relationship.  

Cheers,

Sau

[Anonymous]

Hello @Ashish_Mathur ,

I want to stress that while there was an issue with the cardinality of the relationship, your suggestion was very valuable in fixing the blank row issue. I wish I could accept both answers as the solution.

Thank you,

Sau

[Ashish_Mathur]

You are welcome.

[parry2k]

@CNENFRNL I don't understand the logic why to pass the 2nd parameter to calculate and as a matter of fact, why even calculate is required?? Not sure what I'm missing here.

[CNENFRNL · Accepted solution]

Hi, @parry2k , as mentioned in my former reply, Titles is a lookup table, thus the relationship between Titles and AuthorTitles changes to 1:* accordingly. In order to make a filtering propagate reversely from AuthorTitles(*) to Titles(1), I use an expanded table as filter modifier here. Of coz it's out of conceptual illustration to author such a verbose measure whereas your concise solution is good enough to tackle this practical issue.

[CNENFRNL]

Hi, @Anonymous , in theory, TITLES table should be a lookup table; thus the relationships in the data model is supposed to be built this way,

You might want to try this measure

Count = CALCULATE( COUNTROWS( Titles ), CALCULATETABLE( AuthorTitles ) )

[Anonymous]

Hello @CNENFRNL ,

You were right about the  change of relationship cardinality.

Should be 1-many  and not 1-1.

i.e. One Title has many Authors

I used the DAX expression from   @Ashish_Mathur 

CountPbiCommunity = 
VAR TotalTitle = COUNTROWS(AuthorTitles)
return IF( ISBLANK( TotalTitle ), 0, TotalTitle )

@Ashish_Mathur  , @CNENFRNL 

Thanks both.

[parry2k]

@Anonymous not sure what your expected result is? It should show only 4 rows, 30 and 40 will not show, and not sure why you have that measure, the measure should be  as simple as this

Count = COUNTROWS ( AuthorTitles )

In visualization, use Id and Author from author table and drop above measure and you should get 4 books.

Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020

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.⚡

[Anonymous]

Hi @parry2k ,

This removes the blank row. But, it does not render the 'zero'.   

The relationship cardinality was the issue. 

Thank you,

Sau