Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
77 | |
69 | |
52 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |