Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Solved! Go to 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.
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
@Anonymous, although you have a solution, here is the updated measure
Count = COUNTROWS ( AuthorTitle ) + IF ( NOT ISBLANK ( MAX ( Authors[Author] ) ), 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.
Hello @Anonymous ,
Hi,
You may download my PBI file from here.
Hope this helps.
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
Hi,
I am confused by your reply. Have i answere your question?
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
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
You are welcome.
@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.
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, @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.
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
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 ) )
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
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 )
Thanks both.
@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.⚡
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 @parry2k ,
This removes the blank row. But, it does not render the 'zero'.
The relationship cardinality was the issue.
Thank you,
Sau
User | Count |
---|---|
86 | |
84 | |
69 | |
67 | |
55 |
User | Count |
---|---|
125 | |
100 | |
90 | |
84 | |
66 |