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 everyone,
I need your help regarding Power BI because I'm quite new to it. I'm struggling to compute and show what I want.
I have imported my table from a database that looks like this:
What I want is to compute the sum of the Nominal for each Counterparty and each Currency, BUT it needs to respect the condition: start date <= date in the date table & end date > date in the date table that I created on Power Pivot.
In the end, I want to show the result just like the below image, a dynamic table (ex.if you click on EUR, it will show all EUR data)
Moreover, after computing the sum of the nominal, I need to compute the difference between the two dates and the % variation of it.
On my side, I was able to get the sum I want but the problem is I created computed columns for each counterparty and currency like 'AZE EUR', 'AZE USD', in the Date table.
By doing this, I can't create a pie chart later that defines the % of EUR or USD owned by AZE and HUB counterparties. So no dynamic chart.
I hope I was clear enough. If not let me know 🙂
Thank you in advance for all your help.
Solved! Go to Solution.
Hi, @Merry3010
You need to create another table:
Bridge Table =
GENERATE (
CALENDAR ( DATE ( 2021, 01, 01 ), DATE ( 2021, 12, 31 ) ),
SELECTCOLUMNS (
'Trade Table',
"Counterparty", 'Trade Table'[Counterparty],"Currency",'Trade Table'[Currency],"Counterparty_Currency",'Trade Table'[Counterparty_Currency]
)
)
Measure:
Sum of Nominal =
VAR _currentdate =
SELECTEDVALUE ( 'Bridge Table'[Date] )
RETURN
SUMX (
FILTER (
'Trade Table',
_currentdate >= 'Trade Table'[Start date]
&& _currentdate < 'Trade Table'[End date]
),
'Trade Table'[Nominal]
) + 0
Result:
Please check my attached pbix file for more details.
If it doesn't meet your requirement, please let me know.
Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Merry3010
You need to create another table:
Bridge Table =
GENERATE (
CALENDAR ( DATE ( 2021, 01, 01 ), DATE ( 2021, 12, 31 ) ),
SELECTCOLUMNS (
'Trade Table',
"Counterparty", 'Trade Table'[Counterparty],"Currency",'Trade Table'[Currency],"Counterparty_Currency",'Trade Table'[Counterparty_Currency]
)
)
Measure:
Sum of Nominal =
VAR _currentdate =
SELECTEDVALUE ( 'Bridge Table'[Date] )
RETURN
SUMX (
FILTER (
'Trade Table',
_currentdate >= 'Trade Table'[Start date]
&& _currentdate < 'Trade Table'[End date]
),
'Trade Table'[Nominal]
) + 0
Result:
Please check my attached pbix file for more details.
If it doesn't meet your requirement, please let me know.
Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-easonf-msft ,
I have an issue creating a relationship from "many to many" between tables. I can only do "many to one". So I created a table that takes unique values (AZE-EUR, AZE-USD, HUB-EUR, HUB-USD) connecting the 'Trade table', and the 'Bridge table' and created the measures "sum of nominal" but didn't get the right sum. So I tried as a calculated column in the "bridge table". Still didn't get the right amount.
At work, we don't have the possibility to download the latest version of powerbi (I don't know, if it is related to the power bi version)
Hi, @Merry3010
The 'Bridge Table' is created to replace the original 'Trade Table' to create a ‘many-to-many’ relationship.
Have you checked wherther the fields in your matrix visual are all from the 'Bridge Table' rather than 'Trade Table'?
To avoid missing some important information, please share your current sample pbix file for testing.
Best Regards,
Community Support Team _ Eason
Hi, I made some modifications with your solution, and now it works.
Thanks for your help
Hi, Thank you for your quick reply.
I am not allowed to show a sample of my data, but I will create a new one with the same type of data for testing.
What I am struggling to do is creating the cardinality between the bridge table and the trade table because, on the bridge table I have no unique values in the counterparty_currency column. (This is the reason why I can't create a relationship between 'many to many')
Hi @v-easonf-msft ,
Sorry for the late reply! Thank you very much for your help.
I will try on my data and let you know if it works or not. 🙂
// Assuming the model is correct
// which means the date table is
// DISCONNECTED from the fact table.
// Additionally, assuming that start
// and end date are always present,
// meaning there are no blanks.
[Your Measure] =
// This will work with any
// period of time selected from
// the date table, not only for
// one day. Please understand
// the logic of the measure to
// know what it does.
var MinDateSelected = MIN( 'Date Table'[Date] )
var MaxDateSelected = MAX( 'Date Table'[Date] )
// By the way, Currency and Counterparty should
// be a separate dimension in a proper model.
// They should not be sourced from the fact table.
// All columns in a fact table should be hidden
// since slicing can only be done via dimensions.
// If you deviate from this proper design,
// you may have problems (and sooner or later you
// certainly will).
var IsOneCurrencyVisible = HASONEFILTER( 'Trade Table'[Currency] )
var Result =
if( IsOneCurrencyVisible,
SUMX(
'Trade Table',
var vNominal = 'Trade Table'[Nominal]
var vStartDate = 'Trade Table'[Start Date]
var vEndDate = 'Trade Table'[End Date]
return
vNominal
* (
vStartDate <= MinDateSelected
&&
MaxDateSelected < vEndDate
)
)
)
return
Result
Hi Daxer,
Thank you for your answer. However, the measure doesn't give any output. I only have 0 everywhere.
I have also followed your advise on slicing my counterparty & currency data from the fact table into a separate dimension, based on their trade id.
NB: The date table is not connected to any table & In my real table ,all nominals are different. In the table that I showed above I wrote '100' everywhere as nominal to make it simple
Hi Daxer,
I've tried it on my initial model and it didn't work so I tried on my new model with separate counterparty and currency while changing the measurer accordingly. And didn't work.
Hi, @Merry3010
Did you solve your problem?
If not, please share your sample pbix file for testing.This will better help us understand rhe problem.
Best Regards,
Community Support Team _ Eason
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 |
---|---|
112 | |
99 | |
73 | |
72 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |