Great Video as usual Curbal, I have two questions for you please 1) Using slicers will allow the user to have multiple choices, how can we use something like radio button to select only one value at a time?. 2) Can you make a video to explain to us how to get streaming data from internet, and where can we find web sources of data to provide common subjects, like currencies rates, football results, weather forecasts, etc... so we can use it in our reports with online data, rather than just typing it in a static table. Thank you
Hi Majd, 1.You can return a blank using DAX if a user selects more than one choice. I dont know if there is a custom visual for radio buttons, but you can create your custom visuals if you need to. 2. Here it is an example of a web source for currencies: www.x-rates.com/historical/?from=EUR&amount=1&date=2014-06-03&date=2014-06-03 Here is a video on how create those queries: ruclips.net/video/6PZSZ53iSos/видео.html As to why I enter data statically, I have in previous videos used web live data, but with time those sources either disappeared or they becomed paid sources preventing the tutorial from being reproduced. /Ruth
+Majd Yazigi Not sure what you mean, but if you want to connect to google analytics, here is a video on how to that: m.ruclips.net/video/UJSapgwy5GQ/видео.html /Ruth
Hi, Thanks for the video. selected currency = values(currency[currency]) not working in my case. do I need to create a relationship bw these two tables first?
Muchas gracias por tus aportes Ruth son una excelente guía para los que recien nos adentramos en el mundo del powerbi, respecto a este truco, se podría realizar teniendo 2 slicers, es decir Currency y año?? Eh tradado de integrarlo, pero creo que mis conocimientos no son lo suficientemente amplios para hacerlo funcionar. De antemano gracias.
Muchas gracias por tomarte el tiempo de responderme, creo que plantee mal mi pregunta. Mil disculpas, Por ejemplo: en mi hoja tengo 2 filtros (ciclo, encuesta).. entonces si yo filtro por ciclo 1, quiero que mi measure me muestre ciertos valores, si selecciono ciclo 2 y en mi filtro de encuesta selecciono 1er. Encuesta, entonces ese measure debe de mostrar otro valor. o me funciona cuando tengo seleccionado algún Ciclo, si no selecciono ninguno me muestra error en el measure.. Me podrías apoyar por favor. Mil Gracias!! Saludos desde Mexico
+Gerardo Mujica Arevalo Hola Gerardo! Mexico, que maravilla :) A ver, podrías poner la pregunta en la comunidad de Power BI? Pon también un ejemplo de tus datos! /Ruth
I can't use the format function in chart because format function changes the measure to a text format. is there anyway to use format in chart rather than table?
Thanks a lot for the video and sample file. I was just playing with the file and found an interesting situation which didn't work... What If I want to : 1. Display Product Id, Product name and Total Sales Measures created 2. I want to slice/filter on product by id and name. As soon as I add ID from product table, The table visual has loads of duplicate junk rows, I can't figure out why , and how to avoid these (Except may be using a calculated column in Order_details table to have product name)
My boss wants to put a period slicer and currency slicer. I'm at loss as to how to convert the YTD figures into EUR. Because there're multiple rates over the year. Any hints?
How about there are bank transactions in multiple functional currencies and one presentation currency? Need. a slicer with a choice between functional and presentational currency If u select presentation currency the numbers will convert to USD for example and when yoi select functional currencies the columns or numbers will convert to bank respective currencies ie pound Eur AED etc
Great tutorial! This creates a text field and works perfectly with Table and Matrix - Is there a way that we can format this as a whole number so we can display these in bar charts as well?
such an awesome video, I have a doubt, what if we have data for many years, and for each year we have different conversion rates for each currency for example FY2017 Eur exchange rate 1.77, FY2018 Eur exchange rate 1.82 how to work in this scenario?
Many thanks for that helpful video. Do you know how I can put this values into a bar chart and filter this with slicers? Currently I can't move the Total Sales (selected currency) into the value box of a chart. Thanks in advance
What if you have variable number of currencies? And table of currencies is provided by datamodel and is downloaded from external web resource for example
Is there a way to handle this situation when supported currency may be added and I don't want to replicate that swicth case line again ? Also, We get exchange rate on a Monthly basis .. Any example would be a great help
Hi! What if i have multiple rates (according to months for one currency) and I want to select the most recent month rate, then how could I store only the most recent rate in the selected currency measure? Thanks
Curbal I found below article. At first I thought related would be the trick. But in fact lookupvalue doesnt need a relation between tables. www.dutchdatadude.com/power-bi-pro-tip-lookupvalue-function/
@@CurbalEN my currency rate also change every quarter but I would like to keep the history and apply the rate of the relevant quarter. For instance if data are entered on Q1 2019 the rate used will be different from the rate I would have if the data had been entered on Q4 2018. Do you have a solution for that ? Thank you Mickaël
Hi Michael, I don’t have a video for that, so please ask in the Power Bi community to get help. Here is how and why: m.ruclips.net/video/oXFOebuvRz0/видео.html /Ruth
Thank you for the explanation, your videos are awesome. In my projecet i do need to change the currency rate base on a value defined by the user on the report. Do you have some idea?
@@CurbalEN Great video! I expressed myself wrong. I'm trying to build a report where the user can set the value of the currency in the own report, like a parameter. I found a way creating a table and doing a measure with it, its a workaround but its working. Thanks for your support, your videos are great.
@@JoaoR91 I am trying to do something similar as a POC before trying it in enterprise reporting tool. Can you share the link if you have posted the solution or share the detail
I want to get rate from users then want to convert into that rate . how to do ? like if choosed INR then show INR value, if choose USD then pur rate from numeric range parameter and convert into USD using given rate , not manually put into table then convert
Hello, Thank you so much for this amazing video. It helps me a lot . Can you tell me please i need to create 10 measures if I have 10 columns to convert ? (In this video, you convert one column Total sales). Thank you for the help :)
is Total Sales a Measure ? Because when I use similar formula in my table, I get an error that It returned multiple values (I am applying this on a column).
I need to convert the Currency on the Periodic wise, Like for the Quarter Q1 to Q4 the conversion rate will be varying. So the total sales of the year will calculate based on the different conversion rate. Please help me on this.
Hi!! I found your video really interesting. I would like to know if you could help me because my scenario is a little bit complex. I have inputs in different currencies and then I want to create a sales report where you could select in which currency you want to see the data. Another complexity is that I have a daily exchange rate for 7-10 currencies. How could I built it?Thanks!!
Hi, Having some trouble implementing this approach and cant figure out why: Measure: Selected Currency = VALUES('Currency'[Currency]) Error: Calculation error in measure 'Currency'[Selected Currency]: A table of multiple values was supplied where a single value was expected?
Very Cool Video, I actually did something similar with Units to Value switch, however, I am still trying to find a way to get it into a chart because obviously Format Changes to Text - Any ideas around this?
Very similar to what you have done in this video - if you changed that table to a bar chart and use the measure you created that has the Format function - the Bars in the chart will not appear as the value is not a numeric value but a text value - so I guess my question is, is how can this be implemented into a Chart vis.
+Mark Blackburn Gotcha! I did this video a while ago, had to go and check. What happens if you multiply the measure by 1? Haven't tried it myself, but is should work(?) /Ruth
How can I create Total Sales(Sek) measure. What is it's DAX Query? I used VALUES but it did not work. Error is like this => A table with multiple values is provided while waiting for a single value.
It depends on what you are trying to do and how your model looks like. Post in your question in the power bi community and give as many details as possible to get specific help! /Ruth
Hello, Here is the detailed note about POWER BI issue. - I need a POWER BI report in USD format, but after formatting also i'm seeing reports in INR format. I've tried all the formatting set up in power BI, but still seeing the same issue. Assumption: When I downloaded POWER BI, my system set up in INR, but now I changed as USD. But it's not reflecting in POWER BI. Please assist me to solve the same.
Hi Ruth, This video cannot be used for currency conversion as we also have date field for consideration. This is just slicer application you are showing in the video. No offence your contents are good but in this video its not. Thanks
Hi Amey, What you are mentioning is a different case that what I explain in this video, so in that case you are right. In a future video I can cover your scenario. Ruth
You saved my life! Was trying first to set up a whole nee power Bi report in another currency . Thanks!
🥳🥳
Awesome feature! great learning, thank you!!
Thanks for the currency switch : )
+ExcelIsFun You welcome! Really neat solution for International companies :)
/Ruth
What if the Total sales are in multiples currencies and I want to convert them a single currency like EUR ?
Great Video as usual Curbal, I have two questions for you please
1) Using slicers will allow the user to have multiple choices, how can we use something like radio button to select only one value at a time?.
2) Can you make a video to explain to us how to get streaming data from internet, and where can we find web sources of data to provide common subjects, like currencies rates, football results, weather forecasts, etc... so we can use it in our reports with online data, rather than just typing it in a static table.
Thank you
Hi Majd,
1.You can return a blank using DAX if a user selects more than one choice. I dont know if there is a custom visual for radio buttons, but you can create your custom visuals if you need to.
2. Here it is an example of a web source for currencies:
www.x-rates.com/historical/?from=EUR&amount=1&date=2014-06-03&date=2014-06-03
Here is a video on how create those queries:
ruclips.net/video/6PZSZ53iSos/видео.html
As to why I enter data statically, I have in previous videos used web live data, but with time those sources either disappeared or they becomed paid sources preventing the tutorial from being reproduced.
/Ruth
I watched the video and it is great, is there a website or web source to choose from multiple subjects, Google Analytics for example?
+Majd Yazigi Not sure what you mean, but if you want to connect to google analytics, here is a video on how to that:
m.ruclips.net/video/UJSapgwy5GQ/видео.html
/Ruth
Great. Its working my side also
Yey!
Great video. Sincere thanks!!
😊
What if there are several years so several exchange rates ? Thank you very much
Give details of your case in the power bi community and you will help in no time!
/Ruth
You're a star. Thanks you so much for sharing this useful tip.
Thanks to you for watching :)
/Ruth
Very handy. Thanks for sharing.
Glad it helped :)
/Ruth
Hi, Thanks for the video. selected currency = values(currency[currency]) not working in my case. do I need to create a relationship bw these two tables first?
nice tutorial...
Informative! but the same DAX is not working when I put that in Bar Chart or any other visuals. Please advice
Muchas gracias por tus aportes Ruth son una excelente guía para los que recien nos adentramos en el mundo del powerbi, respecto a este truco, se podría realizar teniendo 2 slicers, es decir Currency y año?? Eh tradado de integrarlo, pero creo que mis conocimientos no son lo suficientemente amplios para hacerlo funcionar. De antemano gracias.
+Gerardo Mujica Arevalo Si claro, simplemente añade el año como slicer (si no recuerdo mal) :)
/Ruth
Muchas gracias por tomarte el tiempo de responderme, creo que plantee mal mi pregunta. Mil disculpas, Por ejemplo: en mi hoja tengo 2 filtros (ciclo, encuesta).. entonces si yo filtro por ciclo 1, quiero que mi measure me muestre ciertos valores, si selecciono ciclo 2 y en mi filtro de encuesta selecciono 1er. Encuesta, entonces ese measure debe de mostrar otro valor.
o me funciona cuando tengo seleccionado algún Ciclo, si no selecciono ninguno me muestra error en el measure.. Me podrías apoyar por favor. Mil Gracias!! Saludos desde Mexico
+Gerardo Mujica Arevalo Hola Gerardo!
Mexico, que maravilla :)
A ver, podrías poner la pregunta en la comunidad de Power BI? Pon también un ejemplo de tus datos!
/Ruth
Claro que sí Ruth, mil gracias !! :D
+Gerardo Mujica Arevalo Mexico es uno de los países que tengo que visitar, por lo que me cuentan, impresionante :)
Saludos,
/Ruth
thx for the tutorial, i have a question, what if yo matrix currency have diferent currency?
Will this work for graphs as well?
awesome video
+Amit Shukla Thanks Amit!
/Ruth
thank you for the tip
thanks when you want this measure to a line or bar chart it is wrong becouse format convert to text...can you help me?
I can't use the format function in chart because format function changes the measure to a text format. is there anyway to use format in chart rather than table?
+sahul hameed Hi Sahul, I got the same question earlier today, I need to check what can be done and let you guys know about it!
/Ruth
@@CurbalEN hi just checking if you had an solution for this?
Can we apply this in any visualization like Pie,Donut, Bar caht, etc...
How can I used it to bar chart? Cz I did try for table view and it worked but it didn't work on Bar Chart, Thank you
Thanks a lot for the video and sample file.
I was just playing with the file and found an interesting situation which didn't work...
What If I want to :
1. Display Product Id, Product name and Total Sales Measures created
2. I want to slice/filter on product by id and name.
As soon as I add ID from product table, The table visual has loads of duplicate junk rows, I can't figure out why , and how to avoid these (Except may be using a calculated column in Order_details table to have product name)
My boss wants to put a period slicer and currency slicer. I'm at loss as to how to convert the YTD figures into EUR. Because there're multiple rates over the year. Any hints?
Hi This will not work in the line charts
then what is the approch i can change
Please clarify the measure which is multiplying with values.
Hello, may I ask what query or measure did you used in total sales (sek)?
How about there are bank transactions in multiple functional currencies and one presentation currency?
Need. a slicer with a choice between functional and presentational currency
If u select presentation currency the numbers will convert to USD for example and when yoi select functional currencies the columns or numbers will convert to bank respective currencies ie pound Eur AED etc
You have video about everything :))
+Emin Uzun 😂Noy yet, but hopefully soon!
/Ruth
Great tutorial! This creates a text field and works perfectly with Table and Matrix - Is there a way that we can format this as a whole number so we can display these in bar charts as well?
Even I need the same one. Let me know if you find any. I want to display in Bar Chart
Can we use it in any visual like piechart, donut series ... ? If it's possible please post the video .
Hi, what is the relationship between the currency table and your data table? I have error with selected currency formula.
Hi Ruthu,
It's cool....but if I have data from 2000 to 2020 , so currency also changed in year bases then how can in wrote?
Thanks in advance
this is what i want to implement in our project..thank you Curbal..is there any way we can do this with two drop down tiles..
+manu prakash Not sure what you mean... But download the sample file and try it to see if it works.
/Ruth
such an awesome video, I have a doubt, what if we have data for many years, and for each year we have different conversion rates for each currency for example FY2017 Eur exchange rate 1.77, FY2018 Eur exchange rate 1.82 how to work in this scenario?
Many thanks for that helpful video.
Do you know how I can put this values into a bar chart and filter this with slicers?
Currently I can't move the Total Sales (selected currency) into the value box of a chart.
Thanks in advance
Hi Ma,
Would you mind posting your question in the power Bi Community? I am on vacation. Thanks!
/Ruth
In the switch case statement what is that "TOTAL SALE(sek)" mean and what values does it have?
Hi Rano,
You can download the file in the description box to see how it is built.
/Ruth
you have not explained from where Total sales(sek) come from? for me it is giving error due to this
What if you have variable number of currencies? And table of currencies is provided by datamodel and is downloaded from external web resource for example
+Sergey Lossev Hi Sergey, you should be able to use the same method. Anything in particular you don't think will work?
/Ruth
Is there a way to handle this situation when supported currency may be added and I don't want to replicate that swicth case line again ?
Also, We get exchange rate on a Monthly basis ..
Any example would be a great help
Hi! Post a case in the power bi community and give as much details as possible to get help.
Thanks!
/Ruth
Hi! What if i have multiple rates (according to months for one currency) and I want to select the most recent month rate, then how could I store only the most recent rate in the selected currency measure? Thanks
Cant u use lookupvalue to look up the currency rate. So u dont have to use switch and typ a line for every currency
+Mathijs Mmmm do you have a sample report? I would love to do a video about it :)
/Ruth
Curbal I found below article. At first I thought related would be the trick. But in fact lookupvalue doesnt need a relation between tables.
www.dutchdatadude.com/power-bi-pro-tip-lookupvalue-function/
+Mathijs Great! Will read and make a video, thanks for the tip 😃!
/Ruth
grate - what happens if the currency rate changes every day?
Then you need to connect to the source instead :)
/Ruth
?Thank you - do you mean to bring all the data to the fact table
.I have 20 currency rate that changes every day
Yes, if you only need the daily rate and the API allows it, you can import only that.
/Ruth
@@CurbalEN my currency rate also change every quarter but I would like to keep the history and apply the rate of the relevant quarter. For instance if data are entered on Q1 2019 the rate used will be different from the rate I would have if the data had been entered on Q4 2018.
Do you have a solution for that ?
Thank you
Mickaël
Hi Michael, I don’t have a video for that, so please ask in the Power Bi community to get help. Here is how and why:
m.ruclips.net/video/oXFOebuvRz0/видео.html
/Ruth
Thank you for the explanation, your videos are awesome.
In my projecet i do need to change the currency rate base on a value defined by the user on the report. Do you have some idea?
Maybe this?
m.ruclips.net/video/Q4hF4CwJsFE/видео.html
/Ruth
@@CurbalEN Great video!
I expressed myself wrong. I'm trying to build a report where the user can set the value of the currency in the own report, like a parameter. I found a way creating a table and doing a measure with it, its a workaround but its working. Thanks for your support, your videos are great.
@@JoaoR91 I am trying to do something similar as a POC before trying it in enterprise reporting tool. Can you share the link if you have posted the solution or share the detail
I want to get rate from users then want to convert into that rate . how to do ? like if choosed INR then show INR value, if choose USD then pur rate from numeric range parameter and convert into USD using given rate , not manually put into table then convert
Hello,
Thank you so much for this amazing video. It helps me a lot . Can you tell me please i need to create 10 measures if I have 10 columns to convert ? (In this video, you convert one column Total sales).
Thank you for the help :)
currency rate changes on daily basis ..how can we give changed value input in power Bi?
is Total Sales a Measure ?
Because when I use similar formula in my table, I get an error that It returned multiple values (I am applying this on a column).
It might be a calculated column, I don’t remember exactly. The download doesn’t work?
/Ruth
unfortunately download doesn't work
Ok, got you! I updated the link, please try again.
/Ruth
Same problem at my end too. Can you please update the link as it doesn't work again - Thanks
I need to convert the Currency on the Periodic wise, Like for the Quarter Q1 to Q4 the conversion rate will be varying. So the total sales of the year will calculate based on the different conversion rate. Please help me on this.
Did you find a solution? I'm also stuck with this :)
Hi!! I found your video really interesting. I would like to know if you could help me because my scenario is a little bit complex. I have inputs in different currencies and then I want to create a sales report where you could select in which currency you want to see the data. Another complexity is that I have a daily exchange rate for 7-10 currencies. How could I built it?Thanks!!
Hi Saray,
Could you post in the power bi community? Give as many details as you can get support quickly!
/Ruth
When I applied this in pie chart, it can't apply DAX measure in value field, because data type issue but working fine in table visualization
i find the same issue, I couldn't use converted values in graphs
not workking for me, its works only if any option is selected. If nothing is selected, its get a error
Hi, Having some trouble implementing this approach and cant figure out why:
Measure:
Selected Currency = VALUES('Currency'[Currency])
Error:
Calculation error in measure 'Currency'[Selected Currency]: A table of multiple values was supplied where a single value was expected?
Your measure returns a table instead of a value.
That is what the error is telling you.
/Ruth
@@CurbalEN This is what is confusing me, I have chosen the column rather than the table.
How we can create Dynamic/Variable Column Header Names changing with Slicer?
muy bueno pero una consulta donde dice ...total sale )select currency).. lo quiero que el resultado tambien me muestre decimales me ayudarias graciass
LUIS ALBERTO LOPEZ CHAVEZ
Solo tendrías que seleccionar la medida y darle formato de decimales. Saludos
+LUIS ALBERTO LOPEZ CHAVEZ Hola Luis Alberto, formatea la formula para que te de los decimales como lo harías en Excel,
/Ruth
+djeritorres Gracias djerirorres! ;)
/Ruth
Curbal
Un placer. Saludos...
+djeritorres igualmente :)
why we can not use the currency symbol dynamic when we use any pia chart of graphs , if we can use please tell me how?
You can do it now why the tabular editor. Search on Google!
@@CurbalEN I want to it on Line chart can we do it here.
I dont think line charts are supported yet.
@@CurbalEN Thank you for your reply
Very Cool Video, I actually did something similar with Units to Value switch, however, I am still trying to find a way to get it into a chart because obviously Format Changes to Text - Any ideas around this?
+Mark Blackburn Hi Mark, not sure what you mean.. what are you trying to do?
/Ruth
Very similar to what you have done in this video - if you changed that table to a bar chart and use the measure you created that has the Format function - the Bars in the chart will not appear as the value is not a numeric value but a text value - so I guess my question is, is how can this be implemented into a Chart vis.
+Mark Blackburn Gotcha! I did this video a while ago, had to go and check.
What happens if you multiply the measure by 1?
Haven't tried it myself, but is should work(?)
/Ruth
Hey, I would put original amount to values and put the conversion to tooltips. It may solve your problem.
I tried "1" :) Got big X. It messes the formula :)
Total Sales (selected currency) var =
var Lnvalor = [Total sales (sek)]*VALUES('Currency'[Currency Rate])
VAR LcFormato = SWITCH(TRUE();
[Selected currency]="EUR";"€0,00";
[Selected currency]="USD";"$0,00";
"0,00kr")
RETURN FORMAT(LnValor;LcFormato)
+Rodrigo Bedoya Zuluaga Hola Rodrigo, RUclips mando este comentario al spam!
Ya está aprobado.
/Ruth
How can I create Total Sales(Sek) measure. What is it's DAX Query? I used VALUES but it did not work. Error is like this => A table with multiple values is provided while waiting for a single value.
It depends on what you are trying to do and how your model looks like. Post in your question in the power bi community and give as many details as possible to get specific help!
/Ruth
Is it possible to get the exchange rates from www.xe.com website and create dynamic exchange rates? Thanks in advance!
If they have an api, it should be possible.
/Ruth
Hello,
Here is the detailed note about POWER BI issue.
- I need a POWER BI report in USD format, but after formatting also i'm seeing reports in INR format.
I've tried all the formatting set up in power BI, but still seeing the same issue.
Assumption: When I downloaded POWER BI, my system set up in INR, but now I changed as USD. But it's not reflecting in POWER BI.
Please assist me to solve the same.
Please report your issue here:
Issues.powerbi.com
/Ruth
@@CurbalEN thank you.
How to format that values into Millions, Thousands
DAX is not picking up my currency field (in your case Total sales(SEK))
how the multiplication happening without relationship on currency?
i dont know why im getting this error-A table of multiple values was supplied where a single values was expected.could you pls help on this.
+brijendraydv Hi,
The necessary value is passed to the Dax measure :)
/Ruth
+brijendraydv You are missing a VALUES somewhere..
/Ruth
Hi Ruth,
This video cannot be used for currency conversion as we also have date field for consideration. This is just slicer application you are showing in the video.
No offence your contents are good but in this video its not.
Thanks
Hi Amey,
What you are mentioning is a different case that what I explain in this video, so in that case you are right. In a future video I can cover your scenario.
Ruth
Amazing video I want a child with you jaja