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

Balaji1414's avatar
Balaji1414
Frequent Visitor
5 years ago

Hi, column output shuld come like this

Anonymous's avatar
Anonymous
Not applicable
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

  • Balaji1414's avatar
    Balaji1414
    Frequent Visitor
    5 years ago

    Hey thanks for your input, same way i have implemented it is working fine now. 
    Here is another one if you can help that woud be great

    I have built around 15 separate reports, all the reports are matrix (visual) data where I have displayed last 4quarters of data for each measure, I have defined BG color for the values based on the performance for each measure for the four quarters. Now I need to build a final report from all these 15 reports, I need to get the data from these measures where the measure performance is poor, bad (I have a logic)