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

[dkpcr5]

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!

[daxer-almighty]

Please show the model.

[dkpcr5]

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

[daxer-almighty]

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

[dkpcr5]

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.

[daxer-almighty]

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