Forum Discussion

Balaji1414's avatar
Balaji1414
Frequent Visitor
5 years ago
Solved

Different data types in a single column

Hi All, In my report i have a table and the data types are different (have $,%,text, numbers in a single column) in columns, i need to add a backround color for the data, any leads please Many than...
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Balaji1414 ，

    Sorry for delay. I updated my sample pbix file(see attachment), please check whether that is what you want.

    Conditional formatting =
    VAR _curcolumn =
    SELECTEDVALUE ( 'Table'[Q1] )
    VAR _includesc =
    IFERROR ( SEARCH ( "$", _curcolumn, 1, 0 ), 0 )
    VAR _scnumber =
    IFERROR ( VALUE ( RIGHT ( _curcolumn, LEN ( _curcolumn ) - 1 ) ), 0 )
    VAR _includepc =
    IFERROR ( SEARCH ( "%", _curcolumn, 1, 0 ), 0 )
    VAR _pcnumber =
    IFERROR ( VALUE ( LEFT ( _curcolumn, LEN ( _curcolumn ) - 1 ) ) / 100, 0 )
    VAR _number =
    IFERROR ( VALUE ( _curcolumn ), 0 )
    RETURN
    SWITCH (
    TRUE,
    _includesc > 0,
    IF ( _scnumber > 500, "#315496", IF ( _scnumber < 500, "#f6af82" ) ),
    _includepc > 0, IF ( _pcnumber > 0.5, "#a9d092", "#ffff01" ),
    IF ( _number > 75, "#00af50", IF ( _number < 50, "#fe0000" ) )
    )

    Best Regards

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi Balaji1414 ,

I created a sample pbix file(see attachment), please check whether that is what you want.

1. Create a measure as below to set the color for different type of data(include $,%,text, number etc.)

Note: You can change the color inside the formula to the color you want.

Conditional formatting = 
VAR _curcolumn =
    SELECTEDVALUE ( 'Table'[Column] )
VAR _includesc =
    IFERROR ( SEARCH ( "$", _curcolumn, 1, 0 ), 0 )
VAR _includepc =
    IFERROR ( SEARCH ( "%", _curcolumn, 1, 0 ), 0 )
VAR _number =
    IFERROR ( VALUE ( _curcolumn ), 0 )
RETURN
    IF (
        _includesc > 0,
        "red",
        IF ( _includepc, "orange", IF ( _number > 0, "purple", "green" ) )
    )

2. Make conditional formatting for background color of the column(Format by Field value)

Color based on a calculation

Best Regards