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 ...
  • 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

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.