Active and Inactive Relationship - Display Chart on Inactive Relationship

[JustDavid]

Power BI Experts,

I'm trying to create a chart where the x-axis comes from the tblData based on the 'Close Date' that's in tblLots_by_CloseDate, but by taking the values (i.e. Counts, SUM, CALCULATE, DIVIDE etc) from the tblProforma_Indexing.

As you can see at the screenshot below, my current active relationship is based on 'Start Date'. Reason being, if I didn't have it this way, data that's pulled from the tblProforma_Indexing would be taking based on the 'Close Date' instead of 'Start Date'

My question is, how can I do my DAX etc, so that even though when I'm going to chart later on, my x-axis being 'Close Date' will get the relevant 'Start Date' that's in the tblLots_by_CloseDate to the table in tblProforma_Indexing?

Thank you!

[lbendlin]

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?

[JustDavid]

@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.

[lbendlin]

Look at your example above. There are two paths to get from tblDate to tblProforma_Indexing.  Power BI doesn't like that.

[JustDavid]

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

[lbendlin]

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.

[JustDavid]

Isn't that what I currently am doing (except the bridge table)?

I have a calendar dimension table (tblDate), and 2 fact tables (tblLots_by_CloseDate and tblProforma_Indexing).

The reason why I need the bridge table is so that when I filter on Product Type (which comes from tblLots_by_CloseDate), I need it to return the result that's on tblProforma_Indexing). Without the bridge table, it won't gets filtered at all.

Assume like you said, I ditch the bridge table, HOW do I make a relationship from tblLots_by_CloseDate and tblProforma_Indexing between the close date in tblLots_by_CloseDate and start date in tblProforma_Indexing?

[Anonymous · Accepted solution]

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 Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

You create the connection from the calendar table. Not between the fact tables.

Consider normalizing the filter attribute.