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.
I have a scenario I cannot get around.
Requirement:
The number of decimal places displayed in a QTY field will be determined by a value in another query table. And I want the field to remain as a decimal data type. I dont want to rely on the "Modelling" tab for Data Type and Format. Auto just doesnt work, and I cannot fix the number of decimals.
Background:
The report will be used by numerous customers, all which may have a different requirement for the display of Qty.
Some may want qty to display 2 decimals, some 4 some 6. The number of decimal places they want is stored in a table.
Testing:
Now I can get this working (Using the below measures) but this leaves the field formatted as a Text - But I dont want it as a text format as it can be used in other arithetic functions and formulas and right aligning it everywhere in Visuals is really annoying
QtyFormat = SWITCH('Settings'[Decimals_Qty])), 1, "0.0", 2, "0.00", 3, "0.000", 4, "0.0000", 5, "0.00000", 6, "0.000000" )
Quantity = FORMAT(CALCULATE(sum('Invoices'[Qty])),[QtyFormat])
Other options considered:
When I use the VALUE function to change the field back to a Decimal - it goes back to the Modelling tab for its display properties and number of decimals to display. The FIXED dax function also creates a text object at the end.
So then I thought I would use M script in the Query Editor and advanced script using the "Round" function. But rounding a whole number e.g 23 to 6 decimals doesnt change the display of that field to 23.000000 as I would want it to. The display of the field is still handled via the Modelling tab.
Has anyone got any other ideas or am I going ot have to live with all my decimals fields in my project being text ?
Hi @shaunwilks,
How about showing them as TEXT in the visual while using VALUE to convert to numbers in a measure? I would suggest you vote up this idea.
Best Regards,
I have tried that but its really tough cause the tooltip on the visual shows different number of decimals to the measure - if you get my drift.
I just think the FORMAT function is very very limited if you are using it for a decimal.
Has next to no function really if its to be used in the majority of visual types.
Anyone else got any suggestions ??
I urge anyone reading this to vote up the idea in the link provided it (I added it today after spending large amounts of time looking for a workaround)
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 |
---|---|
108 | |
98 | |
78 | |
66 | |
53 |
User | Count |
---|---|
139 | |
100 | |
95 | |
85 | |
63 |