Trying to establish relationship between values in 2 tables where 1 value is IN the other in PowerBi

[godinbl]

I have 2 tables:  MeterReadings and Auths and a common field called auth

The MeterReadings data looks like this:

meterref	clientref	clientname	wma	auth	readdate	reading
20476	233141	JAMES LINDSAY ROBINSON	Burdekin Groundwater Management Area	100658	1/08/2024	0.063
20476	233141	JAMES LINDSAY ROBINSON	Burdekin Groundwater Management Area	100658	1/08/2024	0.058
17408	233141	JAMES LINDSAY ROBINSON	Burdekin Groundwater Management Area	100658	31/07/2024	64.809
17408	233141	JAMES LINDSAY ROBINSON	Burdekin Groundwater Management Area	100658	16/06/2024	64.525
8572	177132	CATHERINE AGNES QUAGLIATA; LEO CHARLES ANDREW QUAGLIATA	Burdekin Groundwater Management Area	103387; 618664	22/07/2022	6,389.93
8572	177132	CATHERINE AGNES QUAGLIATA; LEO CHARLES ANDREW QUAGLIATA	Burdekin Groundwater Management Area	103387; 618664	13/06/2023	6,446.00
8572	177132	CATHERINE AGNES QUAGLIATA; LEO CHARLES ANDREW QUAGLIATA	Burdekin Groundwater Management Area	103387; 618664	10/06/2024	6,664.04
8971	238641	MARINA ANN MARSON; MICHAEL MARSON	Burdekin Groundwater Management Area	174413	30/06/2023	1,120.92
8971	238641	MARINA ANN MARSON; MICHAEL MARSON	Burdekin Groundwater Management Area	174413	28/06/2022	1,051.00
8971	238641	MARINA ANN MARSON; MICHAEL MARSON	Burdekin Groundwater Management Area	174413	8/06/2024	1,249.10
15175	172163	ENRICO MIO; PAOLO MIO; MARIO MIO	Burdekin Groundwater Management Area	175218; 621390	25/07/2023	911.281
15175	172163	ENRICO MIO; PAOLO MIO; MARIO MIO	Burdekin Groundwater Management Area	175218; 621390	4/07/2022	895.487
15175	172163	ENRICO MIO; PAOLO MIO; MARIO MIO	Burdekin Groundwater Management Area	175218; 621390	3/09/2024	912.221
17400	249245	AUSTRALIAN CANE FARMS LIMITED	Burdekin Groundwater Management Area	175827; 615762	3/07/2023	6,231.29
17400	249245	AUSTRALIAN CANE FARMS LIMITED	Burdekin Groundwater Management Area	175827; 615762	20/06/2022	5,829.51
17400	249245	AUSTRALIAN CANE FARMS LIMITED	Burdekin Groundwater Management Area	175827; 615762	10/06/2024	6,622.37
17400	249245	AUSTRALIAN CANE FARMS LIMITED	Burdekin Groundwater Management Area	175827; 615762	12/02/2024	6,435.58
17400	249245	AUSTRALIAN CANE FARMS LIMITED	Burdekin Groundwater Management Area	175827; 615762	12/02/2024	6,435.54
15097	241129	JOSEPH LEON CALTABIANO; ROSA CALTABIANO; SALVATORE ALFIO NATALE CALTABIANO	Burdekin Groundwater Management Area	176441; 618668	30/06/2022	878.924

notice that the auth field can have 1 auth or many auths - I am trying to create a relationship between my 2 tables where the auth value in the Auths table is IN the auth field in the MeterReadings table

my table in PowerBi looks like this:

and you can see that when there are multiple auths it cannot calculate the value for Entitlement

This my code for the Entitlement field in the table:

Entitlement = 
VAR _auth = [Auth--auth]
VAR _zone = [Zone]
VAR _vol =
    CALCULATE (
        SUM ( 'Auths'[volumeML] ),
        'MeterReadings'[auth] = _auth )

VAR _amo =
    CALCULATE (
        SUM ( 'SWANs'[amount] ),
        'SWANs'[authorisation] = _auth,
        'SWANs'[expirydate] >= MIN ( 'MeterReadings'[readdate] ),
        'SWANs'[expirydate] <= MAX ( 'MeterReadings'[readdate] )
    )
VAR _thisyear =
    CALCULATE ( SUM ( 'AEs'[thisyear] ), 'AEs'[subcatchment] = _zone )
VAR _sumsd_pa =
    CALCULATE (
        SUM ( 'Auths'[SandDValue] ) + SUM ( 'Auths'[PAValue] ),
        'Auths'[Auth--auth] = _auth
    )
RETURN
    ( _vol + _amo ) * _thisyear + _sumsd_pa

this is not bringing up any errors but obviously this part of the code needs to be changed to account for multiple auths:

VAR _vol =

    CALCULATE (

        SUM ( 'Auths'[volumeML] ),

        'MeterReadings'[auth] = _auth )

I have already split the auth field in the Auths table so it contains single auths

any assistance with this much appreciated

[lbendlin · Accepted solution]

Your meterreadings table is missing a primary key, something that can uniquely identify each reading.

Then you are missing an auth dimension table

And finally you are missing the mapping table between reading IDs and auth values.

See attached for a solution proposal.

[godinbl]

@lbendlin thanks for your response - my question to you was ambiguous and your answer therefore not a solution in itself but you mentioned primary keys and IDs mapping to each table and that was my problem - everything working now - THANK YOU!