Forum Discussion

tom-lenzmeier's avatar
tom-lenzmeier
Icon for Helper II rankHelper II
5 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 de...
Need Help
  • Natarajan_M's avatar
    Natarajan_M
    5 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's avatar
tom-lenzmeier
Icon for Helper II rankHelper II
5 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's avatar
Natarajan_M
Icon for Super User rankSuper User
5 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!