Sales table with 2 CustomerID columns

[nirrobi]

Hi,

I have sales table with 2 customers column (actual + notify)

and client table - table below.

I need to show table with those 2 customers and the salesID + amount - table below.

I have 2 connection between the sales and customer table (nofity in active)

thanks in advenced.

SalesIDClientIDNotifyIDAmount

100	1000	1000	55
101	1001	1009	66
102	1000	1005	44
103	1002	1000	33
104	1003	1010	2

And Customer table:

ClientIDClientName

1000	AAA
1001	BBB
1002	CCC
1003	DDD
1009	EEE
1005	FFF
1010	GGG

I need to show one table as the one below:

SalesIDAmountClientNameNotifyName

100	55	AAA	AAA
101	66	BBB	EEE
102	44	AAA	FFF
103	33	CCC	AAA
104	2	DDD	GGG

I have 2 connection between the sales and customer table (nofity in active)

[Zubair_Muhammad · Accepted solution]

@nirrobi

As MEASURES

Notify_Name =
CALCULATE (
    FIRSTNONBLANK ( Customer[ClientName], 1 ),
    CROSSFILTER ( Sales[NotifyID], Customer[ClientID], BOTH ),
    USERELATIONSHIP ( Sales[NotifyID], Customer[ClientID] )
)

[Zubair_Muhammad]

HI @nirrobi

You can use these calculated columns

ClientName =
RELATED ( Customer[ClientName] )

NotifyName =
CALCULATE (
    VALUES ( Customer[ClientName] ),
    FILTER ( Customer , Customer[ClientID] = Sales[NotifyID] )
)

[Zubair_Muhammad · Accepted solution]

@nirrobi

As MEASURES

Notify_Name =
CALCULATE (
    FIRSTNONBLANK ( Customer[ClientName], 1 ),
    CROSSFILTER ( Sales[NotifyID], Customer[ClientID], BOTH ),
    USERELATIONSHIP ( Sales[NotifyID], Customer[ClientID] )
)

[Zubair_Muhammad · Accepted solution]

@nirrobi

Please see the file attached

[nirrobi]

Many thanks for your help!!!

Much appriciated.

I notice that you can't use the ClientName from the Customer table - make all the row duplicare - see attached pic.

please correct me if I wrong.

thanks again.

Nir