Helper II

4 months ago

Solved

Dynamic Formatting Error

In my values column in an unpivoted table, I am trying to apply dynamc formatting to amounts and dates. Prior to the unpivot action, I converted the date column to decimal and the amount column is decimal.

I then wrote the following measure: SUM(Agreements[Value])
FORMAT: Dynamic

SWITCH(

SELECTEDVALUE(UP_PRD_Agreements[Attribute]),

"Current Price", "$#,##0",

"EffectiveDate", "MM/dd/yyyy",

"0")
The current price is correctly formatted but the effective date displays MM/dd/yyyy -- not a date.

I also tried this:

Display Value =
VAR _Raw = SUM(UP_PRD_Agreements[Value])
VAR _AttrType = SELECTEDVALUE(UP_PRD_Agreements[Attribute])

RETURN
SWITCH(
_AttrType,
"EffectiveDate", FORMAT(_Raw, "MM/dd/yy"),
"Current Price", FORMAT(_Raw, "$#,##0"),
"Current Price Valid From", FORMAT(_Raw, "MM/dd/yy"),
FORMAT(_Raw, "0")
)

I got an error saying 828738814 is out of range for format string.

I'm at my wits end.

Thanks in advance!!!!

Need Help

Natarajan_M
4 months ago
Hi tom-lenzmeier , I just modified the DAX formula you were using and obtained the following result. Can you let me know if this is what you are looking for?
The issue with the sum on the date was causing an overflow error, so I took the maximum value and added it to the date to resolve the problem.

Display Value Fixed = VAR _AttrType = SELECTEDVALUE(UP_PRD_Agreements[Attribute]) VAR _Raw = SUM(UP_PRD_Agreements[Value]) VAR _RawMax = MAX(UP_PRD_Agreements[Value]) RETURN SWITCH( _AttrType, "EffectiveDate", FORMAT( DATE(1899, 12, 30) + INT(_RawMax), "MM/dd/yyyy" ), "Current Price", FORMAT(_Raw, "$#,##0"), "Current Price Valid From", FORMAT( DATE(1899, 12, 30) + INT(_RawMax), "MM/dd/yyyy" ), FORMAT(_Raw, "0") )

dax :

Fixed dax :
Sample file :
Dynamic Formatting.pbix

Thanks,
If this response was helpful in any way, I’d gladly accept a kudo.
Please mark it as the correct solution. It helps other community members find their way faster

8 Replies

Natarajan_M

Super User

4 months ago

Hi tom-lenzmeier , I just modified the DAX formula you were using and obtained the following result. Can you let me know if this is what you are looking for?

The issue with the sum on the date was causing an overflow error, so I took the maximum value and added it to the date to resolve the problem.

Display Value Fixed = 
VAR _AttrType = SELECTEDVALUE(UP_PRD_Agreements[Attribute])
VAR _Raw      = SUM(UP_PRD_Agreements[Value])
VAR _RawMax   = MAX(UP_PRD_Agreements[Value])

RETURN
SWITCH(
    _AttrType,

    "EffectiveDate",
        FORMAT(
            DATE(1899, 12, 30) + INT(_RawMax),
            "MM/dd/yyyy"
        ),

    "Current Price",
        FORMAT(_Raw, "$#,##0"),

    "Current Price Valid From",
        FORMAT(
            DATE(1899, 12, 30) + INT(_RawMax),
            "MM/dd/yyyy"
        ),

    FORMAT(_Raw, "0")
)

dax :

Fixed dax :

Sample file :
Dynamic Formatting.pbix

Thanks,
If this response was helpful in any way, I’d gladly accept a kudo.
Please mark it as the correct solution. It helps other community members find their way faster

tom-lenzmeier

Helper II

4 months ago

I am truly grateful for all of your assistance. I am hopefully only going to call upon you one more time. The slicer I am using, based on your earlier solution, is not behaving correctly. Right now regardless of whether I click on all attributes or changes only, nothing happens. If you recall, I'm hoping that when a user clicks on changes only, they see the non-current/current records and the unchanged ones are not displayed. Here's the data I'm working with. The date values have been converted to integers due to the dynamic formatting.

Base Agreement Number	IsChanged	Attribute	Value	IsCurrent	Version
00159957	Changed	Current Price	1100	1	1
00159957	Changed	Current Price	1300	0	1
00159957	Changed	Current Price	1300	1	2
00159957	Changed	Current Price	1430	1	2
00159957	Changed	Effective Date	46082	0	1
00159957	Changed	Effective Date	46082	1	1
00159957	Changed	Effective Date	46100	1	2
00161305	Unchanged	Current Price	745	1	1
00161305	Unchanged	Effective Date	46082	1	1
00162285	Unchanged	Current Price	57	1	1
00162285	Unchanged	Current Price	60	1	1
00162285	Unchanged	Current Price	175	1	1
00162285	Unchanged	Effective Date	46082	1	1

The dax measure is identical to yours.

Toggle Display =

VAR _Selection =

SELECTEDVALUE(

'Chg Toggle'[ShowFilter],

"All Attributes"

)

VAR _IsChg = MAX(UP_PRD_Agreements[IsChanged])

VAR _Value = MAX(UP_PRD_Agreements[Value])

RETURN

IF(

_Selection = "All Attributes",

_Value,

IF(_IsChg = "Changed", _Value, BLANK())

)

tom-lenzmeier
Helper II
4 months ago
Ok, when I drop DisplayValue in the matrix, it works. The measure I had been using (Display) didn't work in the slicer. Your code works, but your solution seems to be based on the measure and not the formatting? If I apply your code to the dynamic formatting, things go awry.

Toggle Display =
VAR _Selection =
SELECTEDVALUE(
'Chg Toggle'[ShowFilter],
"All Attributes"
)
VAR _IsChg = MAX(UP_PRD_Agreements[IsChanged])
VAR _Value = MAX(UP_PRD_Agreements[Value])
RETURN
IF(
_Selection = "All Attributes",
_Value,
IF(_IsChg = "Changed", _Value, BLANK())
)

tom-lenzmeier
Helper II
4 months ago
Not quite. Based on your earlier response, I have this measure:

VAR _Raw = MAX(UP_PRD_Agreements[Value])
VAR _AttrType = SELECTEDVALUE(UP_PRD_Agreements[Attribute])

RETURN
SWITCH(
_AttrType,
"Effective Date", FORMAT(_Raw, "MMM-dd-yy"),
"Current Price Valid From", FORMAT(_Raw, "MMM-dd-yy"),
"Current Price", FORMAT(_Raw, "$#,##0"),
FORMAT(_Raw, "0")
)
Initially, you gave me this measure, which works in the slicer:
Toggle Display =
VAR _Selection =
SELECTEDVALUE(
'Chg Toggle'[ShowFilter],
"All Attributes"
)
VAR _IsChg = MAX(UP_PRD_Agreements[IsChanged])
VAR _Value = MAX(UP_PRD_Agreements[Value])
RETURN
IF(
_Selection = "All Attributes",
_Value,
IF(_IsChg = "Changed", _Value, BLANK())
)
When you clicked on "All Attributes," all the records are displayed. When you click on "Changes Only," only the changed records are displayed. The formatting in the first one I pasted in looks great.

The slicer is the missing piece:
In your original response, the matrix had the Toggle Display measure without the formatting. Does this make sense?
- Natarajan_M
  Super User
  4 months ago
  Hi tom-lenzmeier ,I do not have the measure "Toggle Display" in the shared Dynamic Formatting PBIX file. I believe you are referring to the other file I shared for the SCD tracking thread.
  
  Dynamic Formatting pbix
  
  Scd tracking pbix :
  
  Thanks
  If you found this helpful, please consider giving it a kudo and marking it as the accepted solution — it goes a long way in helping others facing the same issue.
  
  For more Power BI tips and discussions, let’s connect on LinkedIn:
  https://www.linkedin.com/in/natarajan-manivasagan
  
  Cheers!

Natarajan_M
Super User
4 months ago
I'm using the measure to format the values I didn't use the Dynamic formating .

Dynamic format strings are designed for cases where the measure returns a consistent type across all cells.

Thanks,
If you found this helpful, please consider giving it a kudo and marking it as the accepted solution — it goes a long way in helping others facing the same issue.
For more Power BI tips and discussions, let’s connect on LinkedIn:
https://www.linkedin.com/in/natarajan-manivasagan
Cheers!
- tom-lenzmeier
  Helper II
  4 months ago
  I understand what you're saying. I'm just trying to get the toggle to work. Right now, nothing happens when I click on either all attributes or changes.
Natarajan_M
Super User
4 months ago
Hi tom-lenzmeier , Do you mean a slicer based on the attribute?

Thanks

Forum Discussion

Dynamic Formatting Error

8 Replies

Related content

Dynamic formatting

Dynamic number format

Dynamic String Format

dynamic conditional formatting

Error in dynamic format