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.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

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.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.