Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

empty row with count on related table + 0

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

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Anonymous 

    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.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Anonymous 

    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.