cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dkpcr5
Frequent Visitor

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

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!

 

Lots.PNG

5 REPLIES 5
daxer-almighty
Solution Sage
Solution Sage

Please show the model.

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

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

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.

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

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.