cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nirrobi Member
Member

Sales table with 2 CustomerID columns

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

1001000100055
1011001100966
1021000100544
1031002100033
104100310102

 

 

And Customer table:

ClientIDClientName

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

 

I need to show one table as the one below:

SalesIDAmountClientNameNotifyName

10055AAAAAA
10166BBBEEE
10244AAAFFF
10333CCCAAA
1042DDDGGG

 

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

11111.JPG

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: Sales table with 2 CustomerID columns

@nirrobi

 

As MEASURES

 

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

Re: Sales table with 2 CustomerID columns

@nirrobi

 

Please see the file attached

4 REPLIES 4
Super User
Super User

Re: Sales table with 2 CustomerID columns

HI @nirrobi

 

You can use these calculated columns

 

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

Re: Sales table with 2 CustomerID columns

@nirrobi

 

As MEASURES

 

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

Re: Sales table with 2 CustomerID columns

@nirrobi

 

Please see the file attached

nirrobi Member
Member

Re: Sales table with 2 CustomerID columns

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

1111.JPG