Forum Discussion
Active and Inactive Relationship - Display Chart on Inactive Relationship
Hi JustDavid
Thanks for the reply from lbendlin .
Is the following result you want? If so, please refer to the measure below and the attachment.
Total Amount by Start Date = VAR Startdate = CALCULATETABLE( VALUES(tblLots_by_CloseDate[Start Date]), ALL(tblLots_by_CloseDate), 'tblLots_by_CloseDate'[Close Date] = MAX('tblLots_by_CloseDate'[Close Date]) ) RETURN CALCULATE( SUM(tblProforma_Indexing[Amount]), ALL(tblProforma_Indexing), 'tblProforma_Indexing'[Date] IN Startdate )In my test, there is no active relationship between several tables. If there are still problems, please feel free to let me know.
Best Regards,
Yulia XuIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
You should be able to do this via USERELATIONSHIP. Don't use bridge tables if they introduce ambiguous paths.
Please provide a more detailed explanation of what you are aiming to achieve. What have you tried and where are you stuck?
lbendlin Thank you. I'll try to look more at USERELATIONSHIP function more.
You wrote "don't use bridge tables if they introduce ambiguous paths"...can you elaborate more on this what you meant? Google 'ambiguous' and didn't really understand how does it play in this DAX context.
I'll try to set up a dummy table of it if possible to let you know what I'm trying to accomplish.
But in short, tblLots_by_CloseDate is a table that indicates what lots are sold in what months and with other respective data.
| Lot ID | Product Type | Start Date | Close Date | Proforma ID | ||||
| A101 | MNT | Mar 15, 2024 | Oct 30, 2024 | PF-100 | ||||
| B555 | MNT | Apr 15, 2024 | Dec 12, 2024 | PF-100 |
tblProforma_Indexing is Detail table that has cost codes that I need to sum up by it's period
| Proforma ID | Cost Code | Date | Amount | |||
| PF-100 | CC-01 | Mar 15, 2024 | 10 | |||
| PF-100 | CC-01 | Apr 15, 2024 |
| 15 | ||
| PF-100 | CC-01 | May 15, 2024 | 20 | |||
| PF-100 | CC-100 | Mar 15, 2024 | 80 | |||
| PF-100 | CC-100 | Apr 15, 2024 | 90 | |||
| PF-100 | CC-100 | May 15, 2024 | 100 |
So visualize for a moment here that I'm trying to make a chart where my x-axis is based on the close date (remember that this close date is coming from tblDate).
In my super simple small data above, I'd have 1 count plotted in the month of Oct 2024 for A101 and 1 count plotted in the month of Dec 2024 for B555.
The amount however, I'd like it to be based on tblProforma_Indexing.
Lot ID A101, which has Proforma ID of PF-100, has a Start Date of Mar 15, 2024, I'd then SUM all tblProforma_Indexing[Cost Code] where the [Date] is Mar 15, 2024, which in this case is CC-01, CC-100 (10 + 80) and plot 90 in the x-axis of Oct 2024, NOT Mar 2024.
Similarly, for Lot ID B555, I need to plot 105 in the x-axis of Dec 2024 NOT Apr 2024.
Look at your example above. There are two paths to get from tblDate to tblProforma_Indexing. Power BI doesn't like that.
For the date, I'd be pulling it from the tblDate table, as company isn't using regular calendar, but fiscal calendar.
And because I've grouped the months and quarters by it's fiscal, so I won't be pulling date from any other table but from the tblDate table.
However, like I said, I don't know how to have PowerBI "communicate" amongst these tables, especially plottint counts by close date, but taking the $ value from sale date of a different table
Ditch the bridge table. Use proper dimension and fact tables. Use a calendar dimension table that includes your fiscal calendar logic. Don't join fact tables directly, control them via common dimensions.