how to make relationship with two columns in BI data model

[osavaso]

I have two tables with ATMs data and second table with transaction data made through this ATMs.

In ATM data table I have column with some attributes for each ATM.
In example such attributes is IND and OUTD. Also in this table I have SNAP_DATE for ATMs. ATM can change atribute depending on snap date.

In final result I want to receive connection between this two tables depending on: ATMs[ATM#]-->transaction data[ATM#] and ATMs[SNAP_DATE]-->transaction data[SNAP_DATE] to receive breakdown of transactions by IND and OUTD ATMs in months.

My problem is that I want to do this, without making any new merged unique columns.
if it's possible I want make relation between this two tables using just two source columns, which I mentioned above ATM# and SNAP_DATE.

please help me find solution if such exists. I'm new in Power BI so forgive me if I'm asking something stupid.

Thanks in advance.

ATM data Table:

ATM#	IND/OUTD	SNAP_DATE
ATM1	IND	9/30/2019
ATM2	IND	9/30/2019
ATM3	OUTD	9/30/2019
ATM4	OUTD	9/30/2019
ATM1	OUTD	10/31/2019
ATM2	OUTD	10/31/2019
ATM3	IND	10/31/2019
ATM4	IND	10/31/2019

transaction data table:

ATM#	DATE	VOLUME	SNAP_DATE
ATM1	9/1/2019	1	9/30/2019
ATM2	9/2/2019	1	9/30/2019
ATM3	9/3/2019	2	9/30/2019
ATM4	9/4/2019	2	9/30/2019
ATM1	10/1/2019	1	10/31/2019
ATM2	10/2/2019	1	10/31/2019
ATM3	10/3/2019	2	10/31/2019
ATM4	10/4/2019	2	10/31/2019

[jdbuchanan71 · Accepted solution]

Hello @osavaso

You can use TREATAS to create a virtual relationship in a measure.

Volumn TREATAS = 
CALCULATE(
    SUM('Transaction data'[VOLUME]),
    TREATAS (
        SUMMARIZE ( 'ATM data', 'ATM data'[ATM#], 'ATM data'[SNAP_DATE] ),
        'Transaction data'[ATM#],
        'Transaction data'[SNAP_DATE] 
    )
)

Then you can pull fields in from your 'ATM data' table and put this measure on the visual and it will apply the ATM# and SNAP_Date as filters in the measure.

This works even with no relationship between the tables.  The down side is it can be slower than the physical relationship you would get from the comined column you mention.

I have attached my sample file you to look at.

If this solves your issues please mark it as the solution. Kudos 👍 are nice too.

[osavaso]

Thank you very much! This is exactly what I wanted.

One more question. As I understand there is no way to do this by physical relationships between tables? I mean we can't use two connections between two same tables, yes?

[jdbuchanan71]

That is correct, you can only have one active relationship between tables.