Forum Discussion
nirrobi
Helper V
Helper VSales 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
| 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)
As MEASURES
Notify_Name = CALCULATE ( FIRSTNONBLANK ( Customer[ClientName], 1 ), CROSSFILTER ( Sales[NotifyID], Customer[ClientID], BOTH ), USERELATIONSHIP ( Sales[NotifyID], Customer[ClientID] ) )
4 Replies
- Zubair_Muhammad
Community Champion
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
As MEASURES
Notify_Name = CALCULATE ( FIRSTNONBLANK ( Customer[ClientName], 1 ), CROSSFILTER ( Sales[NotifyID], Customer[ClientID], BOTH ), USERELATIONSHIP ( Sales[NotifyID], Customer[ClientID] ) )- Zubair_Muhammad