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 3 different sales measures A, B and C coming from Fact sales table. Measure A shows data with $ (Eg: $ 123 ), Measure B shows data with Cents (Eg: ¢ 123 ) and Measure C shows data with € symbol (Eg: € 123). I also have a slicer in my report called CurrencyName where I have 3 values (USD, Cent and EUR).
CurrencyTable
Currency ID | CurrencyName | Format |
1 | USD | $#,##0 |
2 | Cent | ¢#,##0 |
3 | EUR | €#,##0 |
I wrote a simple measure with if else condition based on the CurrencyName selection, I should call the required measure accordingly.
DynamicMeasure =
VAR __MeasureToFormat =
IF(SELECTEDVALUE('Currency'[CurrencyName])="USD",[Measure A],
IF(SELECTEDVALUE('Currency'[CurrencyName])="Cent",[Measure B],
IF(SELECTEDVALUE('Currency'[CurrencyName])="EUR", [Measure C])))
VAR __Format = SELECTEDVALUE('Currency'[Format])
VAR __FormatedMeasure = FORMAT(__MeasureToFormat1, __Format)
VAR __CheckforBlanks =
IF(NOT(ISBLANK(__MeasureToFormat)), __FormatedMeasure)
RETURN
__CheckforBlanks
Challenge: If I use this DynamicMeasure in any of the visuals in Power BI (except table and matrix) I see no data. But, if I turn the same visual to a table visual or a matrix visual, i see the data coming as expected.
Findings:
1. Individually Measure A, B and C are working fine as expected. But If I make it dynamic (DynamicMeasure) then I'm facing the above mentioned challege.
2. I only noticed this issue if I include the currency symbol. If I remove the __Format section in my DynamicMeasure and only retrive __MeasureToFormat then DynamicMeasure also working as expected. However, my requirement is to show the data with currency symbols.
Looking forward for valuable suggestions.
Thank you in advance!!
@Anonymous , I have solved this using creating Calculation Groups in AAS. Thanks for your help!!
Hi @Anonymous
By default, Power BI visuals handles the measures ( numerical values). The moment you format the measures with currency , the numerical values are converted to char format and hence the visuals are not displaying the results. In other hand, Tables and matix can display the char values and hence you are seeing those values in Table/ matrix.
To dispaly the values in visuals - need to make sure you use "numerical" measure.
Thanks
Raj
Thanks for your response. Can you please help me converting my DynamicMeasure to a numerical measure (should have currency symbol)?
When you add some symbol/char to a measure, it is no more numerical. The alternat option is , based on the selection you can show some legend/message in the visual ( the numbers are in USD ).
Thanks
Raj
But if I use Individual measure which shows data with currency symbol in any visual it works. It only happens with the dynamic option
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 |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |