Forum Discussion

TAD80's avatar
TAD80
Frequent Visitor
2 years ago
Solved

Add column value if it doesn't exist

Hi, 

I am looking for help please. Not sure if this belongs here or in the power query section. Any help or guidance much appreciated. I have a table with data similar to the below sample. There is no Critical risk in the table so I would like to add a calculated column to look at the Rating colum and if Critical is not listed the add it so that the chart show's that there is no critical risks:

RiskRating
Risk 1Low
Risk 2Low
Risk 3Medium
Risk 4Low
Risk 5Medium
Risk 6Low
Risk 7Low
Risk 8High
Risk 9Low
  • Joe_Barry's avatar
    Joe_Barry
    2 years ago

    Hi TAD80 

     

    • Create a Dimension Table for the Ratings. Add all possible ratings in here, including Critical Risk.
    • Make sure that they are spelt the same as what is in your table
    • Create a one to many relationship from your new table's column to the Ratings column and make sure that it is active
    • In you bar chart add the new column from the new table in the x axis.
    • Then add the measure Count of Risk. It won't show Critical Risk at this stage
    • On the right side where you added the new column name, right click and choose "Show items with no data"
    • You will then see in the x axis, the Critical Risk column. It will be empty, but you see in the axis that there are none.

     

    Hope this helps

    Joe

3 Replies

  • Joe_Barry's avatar
    Joe_Barry
    Solution Sage
    2 years ago

    Hi TAD80 

     

    • Create a Dimension Table for the Ratings. Add all possible ratings in here, including Critical Risk.
    • Make sure that they are spelt the same as what is in your table
    • Create a one to many relationship from your new table's column to the Ratings column and make sure that it is active
    • In you bar chart add the new column from the new table in the x axis.
    • Then add the measure Count of Risk. It won't show Critical Risk at this stage
    • On the right side where you added the new column name, right click and choose "Show items with no data"
    • You will then see in the x axis, the Critical Risk column. It will be empty, but you see in the axis that there are none.

     

    Hope this helps

    Joe

    • TAD80's avatar
      TAD80
      Frequent Visitor
      2 years ago

      Thank very much Joe This worked for me!

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi TAD80 

     

    Joe_Barry Thank you very much for your prompt reply. Please allow me to provide examples here.

     

    “Data Table”

     

    "Rating Table"

     

     

    Instead of calculating columns, you should create a measure.

     

    Count Risk = 
IF(
    SELECTEDVALUE('Rating Table'[Rating]) = SELECTEDVALUE('Data Table'[Rating]),
    CALCULATE(
        COUNT('Data Table'[Risk]), 
        FILTER(
            ALL('Rating Table'), 
            'Rating Table'[Rating] = MAX('Rating Table'[Rating])
        )
    ),
    0
)

     

    Here is the result.

     

     

    Regards,

    Nono Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.