Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Combine 3 column names in same list to Use Same Conditional Color Parameters

I have 3 columns within a table that provide conditional color status with words "Complete", "In-Work", "Not Started".   STEP1: I've added a measure as follows (which only applies to 1 of the 3 col...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    GOT CONDITIONAL FORMATTING WORKING! – HERE’S HOW:

    ===============================================================

    Created a column for each of the 3 status columns with an associated numerical value to verify desired outcome while the conversion from text to numeric to color occurred.

    ===============================================================

    MEASURES CREATED:

    FILES TYPE NUMBER:

    Files Type Number =

    VAR STN = MAX(Table1[Step 1:FilesSent/Received])

    var status_number = SWITCH(STN,"Complete",1,"In-Progress",2,"Not Started",3)

    RETURN status_number

     

    INTERFACE NUMBER:

    Interface Number =

    VAR INTERFACE_VALUE = MAX(Table1[Step 3:Automated HMC Interface Complete (Due)])

    var interface_number = SWITCH(INTERFACE_VALUE,"Complete",1,"In-Progress",2,"Not Started",3)

    RETURN interface_number

     

    RECONCILIATION NUMBER:

    Reconciliation Number =

    VAR RECONCILIATION_VALUE = MAX(Table1[Step 2:Reconciliation Built])

    var reconciliation_number = SWITCH(RECONCILIATION_VALUE,"Complete",1,"In-Progress",2,"Not Started",3)

    RETURN reconciliation_number

     

    ===============================================================

    Then created 3 more measures creating 3 more columns for cross-verification:

    ===============================================================

    MEASURE CREATED FOR EACH:

    STATUSColors:

    STATUSColors = IF( [Files Type Number] = 1, "#85ff33",IF( [Files Type Number] = 2, "Yellow", "Red"))

     

    INTERFACE COLORS:

    INTERFACE COLORS = IF( [Interface Number] = 1, "#85ff33",IF( [Interface Number] = 2, "Yellow", "Red"))

     

    RECONCILIATION COLORS:

    RECONCILIATION COLORS = IF( [Reconciliation Number] = 1, "#85ff33",IF( [Reconciliation Number] = 2, "Yellow", "Red"))

     

    ===============================================================

    Then, the 3 main fields holding text status info, I selected each field in the VALUES area and set the drop-down CONDITIONAL FORMAT settings as follows:

    Conditional Formatting> Background Color > Format by FIELD VALUE, BASED ON FIELD "STATUSColors" or "INTERFACE COLORS" or "RECONCILIATION COLORS"

    (this applies the rules to the text columns) (these rules were set up in your measures steps previously)

    ==============================================================

    A special thanks to kentyler for help to resolve this challenge.  Hoping it helps others new to PBI with breaking out the rudimentary parts to make colorization with TEXT work. Numbers is a breeze, text a bit more of a challenge.

  • Anonymous's avatar
    Anonymous
    6 years ago

    Here's the associated pic showing the breakdown explained above..

kentyler's avatar
kentyler
Icon for Solution Sage rankSolution Sage
6 years ago

Try using & to concatenate fields.   'table'[field] & 'table'[other field]

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    no, kentyler could not get it to work.. =-(  If the below is not what you meant, please copy/paste/edit to be exactly to what you meant for me to change) - also got the attached error (image)

    Status Type Number = 
VAR StatusType = SELECTEDVALUE('Table1'[FilesSent/Received] & 'Table1'[Reconciliation Built] & 'Table1'[Automated Interface Complete (Due)])
RETURN

SWITCH(TRUE(),
StatusType = "Complete", 1,
StatusType = "In-Progress", 2,
StatusType = "Not Started", 3
)

    • kentyler's avatar
      kentyler
      Icon for Solution Sage rankSolution Sage
      6 years ago

      here is the dope on SELECTEDVALUE

      The function SELECTEDVALUE returns the value of the column reference passed as first argument if it is the only value available in the filter context, otherwise it returns blank or the default value passed as second argument. Here are a few examples of possible syntax.

      SELECTEDVALUE ( Table[column] )
      SELECTEDVALUE ( Table[column], "default value" )
      SELECTEDVALUE ( Table[column], 0 )

       

      so you probably need something like 

      var Files = SELECTEDVALUE('Table1'[FilesSent/Received])
      var ReconBuilt = SELECTEDVALUE( 'Table1'[Reconciliation Built])
      var InterfaceComplete = SELECTEDVALUE('Table1'[Automated Interface Complete (Due)])
      var statustype = Files & REconBuilt & InterfaceComplete

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Wow! that's kool kentyler I like the way you laid that out... but it's not working just yet because the "ColorStatus" chunk is also still looking at only one table... and since it's not using the x I'm at a loss on how to change it to also ALLOW use of 3 diff tables for those specified colors??

         

        Here's exactly how I updated my first chunk: (no errors so it appears to like it!)

        Status Type Number = 
VAR Files =SELECTEDVALUE(Table1[FilesSent/Received])
VAR ReconBuilt = SELECTEDVALUE(Table1[Reconciliation Built])
VAR InterfaceComplete = SELECTEDVALUE(Table1[Automated Interface Complete (Due)])
VAR StatusType = Files & ReconBuilt & InterfaceComplete
RETURN

SWITCH(TRUE(),
StatusType = "Complete", 1,
StatusType = "In-Progress", 2,
StatusType = "Not Started", 3
)

         

        Here's the 2nd chunk that I'm guessing it keeping the table from fully colorzing all 3 columns properly (bc it only references 1 col): How do I fix this chunk?

        ColorStatus = IF( 'Table1'[FilesSent/Received]="Complete", "#85ff33",IF( 'Table1'[FilesSent/Received]="In-Progress", "Yellow", "Red"))