Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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)
Solved! Go to Solution.
As MEASURES
Notify_Name = CALCULATE ( FIRSTNONBLANK ( Customer[ClientName], 1 ), CROSSFILTER ( Sales[NotifyID], Customer[ClientID], BOTH ), USERELATIONSHIP ( Sales[NotifyID], Customer[ClientID] ) )
Please see the file attached
HI @nirrobi
You can use these calculated columns
ClientName = RELATED ( Customer[ClientName] )
NotifyName = CALCULATE ( VALUES ( Customer[ClientName] ), FILTER ( Customer , Customer[ClientID] = Sales[NotifyID] ) )
As MEASURES
Notify_Name = CALCULATE ( FIRSTNONBLANK ( Customer[ClientName], 1 ), CROSSFILTER ( Sales[NotifyID], Customer[ClientID], BOTH ), USERELATIONSHIP ( Sales[NotifyID], Customer[ClientID] ) )
Please see the file attached
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
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |