Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
26 | |
20 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
43 | |
19 | |
18 |