topic Re: How to show BLANK in matrix header if any records are blank in DAX Commands and Tips

How to show BLANK in matrix header if any records are blank

Anonymous — Fri, 09 Jul 2021 17:58:28 GMT

Hi there,

I have a matrix visual using "Accounts," "Positions," and "Lots". An account can have multiple positions, and a position can have multiple lots.

1 Account --> multiple Positions

1 Position --> multiple Lots

Some lots have "Close Dates." What I'd like to have happen is, if a position has any lots with blank close dates, return blank in the matrix header for that position. If all lots have close dates in a position, return the latest lot close date.

So, in the attachment below: If the crossed out record were blank, the header at the position-level would be blank as well.

I'd like to use this modified "lot close date" field in the matrix, so the same logic applies at the account level as well: if any child is blank, the matrix header is blank as well. Else, return the latest.

Any help would be appreciated. Thanks!

Re: How to show BLANK in matrix header if any records are blank

daxer-almighty — Fri, 09 Jul 2021 19:33:43 GMT

Please show the model.

Re: How to show BLANK in matrix header if any records are blank

Anonymous — Fri, 09 Jul 2021 19:40:11 GMT

Not sure what you mean. A screenshot of the schema?

Re: How to show BLANK in matrix header if any records are blank

daxer-almighty — Fri, 09 Jul 2021 20:34:26 GMT

Yes. To write DAX one has to know how tables are linked and which attributes belong to which tables.

Re: How to show BLANK in matrix header if any records are blank

Anonymous — Mon, 12 Jul 2021 12:32:51 GMT

Very good. Schema below. Basically, TAXLOT_DETAILS has a M-M relationship with POSITION_DETAILS** and ACCOUNT_DETAILS has a 1-M relationship with POSITION_DETAILS.

** M-M relationships are dangerous, but in this case, believed to be OK, since the field causing the M-M relationship isn't used in the model. In a perfect schema, I'd just remove the offending record. I'll get there, but I think that's a separate issue.

Re: How to show BLANK in matrix header if any records are blank

daxer-almighty — Fri, 09 Jul 2021 22:07:51 GMT

All I can see from the picture is that the model is by all appearances AMBIGUOUS. You've got 2 different paths that filter Position_Details: 1) directly from Account_Details and 2) indirectly from Account_Details through Taxlot_Details and then to Position_Details. If you go to YT and look for a vid by Alberto Ferrari on composite models, you'll see why such a design should be abandoned in favour of a different model devoid of this fatal flaw. The same issue is present in another path: from Navigation_Account to Position_Details. This model is BAD. Very bad. Seriously, if you don't do this right now, you are shooting yourself in the foot.

"M-M relationships are dangerous, but in this case, believed to be OK, since the field causing the M-M relationship isn't used in the model."

As much as I can tell you, the fact that a field is "not used in the model" is irrelevant. You've got 2 issues in the model that make it essentially unusable.

Please watch this: https://youtu.be/x3m7qzsVJqQ

and read this: Use composite models in Power BI Desktop - Power BI | Microsoft Docs