Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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
Solved! Go to 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.
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.
I'm not sure, but perhaps something like this will help solve your problem
User | Count |
---|---|
84 | |
76 | |
75 | |
43 | |
36 |
User | Count |
---|---|
109 | |
56 | |
52 | |
48 | |
43 |