Values in multiple columns of one table to reference and sort themselves by a column in another tabl

[Souvik0812]

I have a Project table which looks like this:

Project id	Overall RAG	Fin RAG	Bud RAG	Sch RAG
Project-1	Red	Red	Green	Amber
Project-2	Grey	Amber	Grey	Red
Project-3	Grey	Red	Red	Amber
Project-4	Red	Red	Grey	Red
Project-5	Grey	Amber	Green	Grey
Project-6	Amber	Red	Grey	Green
Project-7	Red	Green	Green	Red
Project-8	Red	Grey	Green	Amber
Project-9	Green	Red	Amber	Green
Project-10	Red	Green	Red	Red

I need these RAG columns to be sorted (individually, whenever whichever is used/sorted) by referencing the sort values from the table below:

RAG	Sort Order
Red	1
Amber	2
Green	3
Grey	4

I mean when I am using any of the RAG columns it should get sorted referening this Sort Order table. I can create multiple sort order table for each RAGs, or I can create sort order columns for each RAG in the same Project table. But that would create redundant data and hence I am trying to keep only 1 Sort order table and reference all RAGs to sort using this table.

Is it possible? Apologies if I couldn't express my issue clearly, please let me know if further info/clarification needed.

Thanks in advance 🙂

[JFarq]

Did you find a solution for this?

I have a similar issue.

I'm trying to sort the survey questions responses in matrices in a specific order (Strongly Agree, Agree, Neither agree nor disagree, disagree, strongly disagree) using a single table as a reference for multiple questions/fields:

I've tried using the following measure Activity is meaningful sort by 2 (based on this question solution)

Activity is meaningful sort by 2 = 
CALCULATE (
    SELECTEDVALUE ( LikertResponseOrderConnect[Name Order Index] ),
    CALCULATETABLE(
        MasterWithValidEntries2,
        USERELATIONSHIP ( MasterWithValidEntries2[Activity is meaningful], LikertResponseOrderConnect[Likert Question Response] ),
        ALLEXCEPT(LikertResponseOrderConnect, LikertResponseOrderConnect[Likert Question Response])
    )
)

It creates the new column with associated reference numbers:

But I get a circular dependency error message when I try to sort my Activity is meaningful survey question responses field by this new column.

Is there a way of doing this without having to create a separate table for each survey question response field?

Thanks for reading.

[krishb1414]

Hi @Souvik0812  If you have same values in all the columns then why don't you use UNPIVOT. So that you can able to get all the colors in one column then create calculated column like 
    Red = 1

    Amber = 2

    Green = 3

    Grey = 4

Finally sort your  RAG column with this calculated column.

If you have any doubts feel free to ask me.

Regards,
Krish

[Souvik0812]

Hi @krishb1414 , thanks for your reply.

If I UNPIVOT the RAG columns it will create multiple rows for each project, the table will increase hugely in size as there are several columns other than the Project Id and RAG columns. Also, the Project ID won't serve as a Primary Key in the table anymore. There are many other RAGs as well, I only created an example with some of them.

Project Id	Attribute	Value
Project-1	Overall RAG	Grey
Project-1	SchedRAG	Grey
Project-1	ScopeRAG	Grey
Project-1	FinRAG	Grey
Project-1	BenRAG	Grey
Project-1	ResouRAG	Grey
Project-1	RiskO	Grey
Project-1	StakeRAG	Grey
Project-2	Overall RAG	Amber
Project-2	SchedRAG	Amber
Project-2	ScopeRAG	Green
Project-2	FinRAG	Green
Project-2	BenRAG	Green
Project-2	ResouRAG	Green
Project-2	RiskO	Green
Project-2	StakeRAG	Amber
Project-3	Overall RAG	Green
Project-3	SchedRAG	Green
Project-3	ScopeRAG	Green
Project-3	FinRAG	Green
Project-3	BenRAG	Green
Project-3	ResouRAG	Green
Project-3	RiskO	Green
Project-3	StakeRAG	Green

[krishb1414]

Hi @Souvik0812  You can create dimnesion table by using Project id and create relationship.