empty row with count on related table + 0

[Arno123]

Hi

My first post, so appologies for the clumsiness.

So I have 2 tables, Documents and Attachments.

To keep it simple, documents has 1 relevant column witch is the HeaderId (the ID of the document).

The Attachments also have only 1 relevant column witch is ID

So I am looking to have a table where I get how many attachments a certain document has.
looks like this:

HeaderID	NrOfAttachments
Doc1	0
Doc2	5
Doc3	0
Doc4	3

The first problem I managed to overcome was showing the rows with a 0 as with the count of the Attachment ID's, rows with 0 would not show.

This I did through a measure on the Documents table where the code is:

NrOfAttachments = COUNTROWS(FILTER(documentAttachment, CONTAINS(questionaireQuestions, questionaireQuestions[Id], documentAttachment[No]))) + 0

However when I Add the measure to the table to display, I get an empty row. Like this:

HeaderID	NrOfAttachments
	0
Doc1	0
Doc2	5
Doc3	0
Doc4	3

I found a similar issue here: How to remove empty rows but keep zero counts in c... - Microsoft Fabric Community

And tansformed the measure code to:

NrOfAttachments =

VAR _a =

    COUNTROWS(FILTER(documentAttachment, CONTAINS(questionaireQuestions, questionaireQuestions[Id], documentAttachment[No]))) + 0

RETURN

    IF ( ISBLANK ( _a ), 0, _a )

But that also did not work.

Any one got a solution where I don't have to pollute the filters?

Kind regards
Me

[Anonymous · Accepted solution]

Hi @Arno123 

After testing, if your data have the blank field, it will return the blank field.

e.g 

Then it will return the blank to 0

You can check it and change the code to the following

NrOfAttachments =
VAR _a =
    COUNTROWS (
        FILTER (
            documentAttachment,
            CONTAINS (
                questionaireQuestions,
                questionaireQuestions[Id], documentAttachment[No]
            )
        )
    ) + 0
RETURN
    IF (
        SELECTEDVALUE ( questionaireQuestions[HeaderID] ) <> BLANK,
        IF ( ISBLANK ( _a ), 0, _a )
    )

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ahmedx]

I'm not sure, but perhaps something like this will help solve your problem

https://youtu.be/eosB-n7m2x8