Get RAG by Project but always keep one extra row for dedicated Project

[Jyaul1122]

I have two table Fact and Project Dim and related with Project column one to many.

DistrictProjectRAGGeo

Resort	P1	1Red	Geo1
Resort	P2	2Amber	Geo2
Resort	P1	3Green	Geo1
Resort	P2	1Red	Geo2
Resort	P3	1Red	Geo3
Delta	P4	2Amber	Geo4
Delta	P5	2Amber	Geo5
Delta	P5	3Green	Geo5
Delta	Delta	Boundry	Geo9
Resort	Resort	Boundry	Geo10

and Project DIM

DistrictProject

Resort	P1
Resort	P2
Resort	P3
Delta	P4
Delta	P5
Delta	Delta
Resort	Resort

I have slicer , Project DIM and Geo.

 Whenever I select any slicer Project DIM and Geo , then I need to calculate rag that shows always rag of selected project + always RAG of of that Project (where Project = "Resort" or "Delta" based on District slicer)

Example: If i select Project P1 or Geo1 then output will be:

if I select P2 or Geo2 then:

if i select (P1 or Geo1) and (P2  or Geo2) then :

if I select (P4 or Geo4) then:

report:

I tried to write measure 

MAX('Fact'[rag]), that does not give proper RAG, how can i do using dax

[v-saisrao-msft]

Hi @Jyaul1122,

Have you had a chance to review the solution we shared by @MFelix? If the issue persists, feel free to reply so we can help further.

Thank you,

[MFelix]

Hi @Jyaul1122 ,

Try the following code:

RAG =
CALCULATE (
    VAR maximum_District =
        VALUES ( Dim[District] )
    VAR District =
        MAXX ( FILTER ( 'Fact', 'Fact'[Project] IN maximum_District ), 'Fact'[RAG] )
    VAR Project =
        MAXX (
            FILTER ( 'Fact', 'Fact'[Project] IN VALUES ( Dim[Project] ) ),
            'Fact'[RAG]
        )
    RETURN
        IF ( District = BLANK (), Project, District ),
    CROSSFILTER ( 'Fact'[Project], Dim[Project], NONE )
)

My only question is concerning the GEO since when you refer geo 1 and p1 the resort is the value geo 10 do you want to overwrite that geo from the filter also?

[Jyaul1122]

@MFelix 

Its work fine, when we sliced upto Project Level but when we sliced upto Geo level the Resort is being filtered.

I would like to have Resort also there.

Result as per your measures:

requirements:

[MFelix]

Hi @Jyaul1122 ,

Create a table for the Geo with unique values then try the following update of the measure:

RAG = 
CALCULATE (
    VAR maximum_District =
        VALUES ( Dim[District] )
    VAR maximum_geo =
        VALUES ( Geo[Geo] )
    
    VAR District =
        MAXX ( FILTER ( 'Fact', 'Fact'[Project] IN maximum_District  ), 'Fact'[RAG] )
    VAR Project =
        MAXX (
            FILTER ( 'Fact', 'Fact'[Project] IN VALUES ( Dim[Project] ) && 'Fact'[Geo] in maximum_geo ),
            'Fact'[RAG]
        )
    VAR Geo =
        MAXX ( FILTER ( 'Fact', 'Fact'[Project] IN maximum_District ), 'Fact'[RAG] )

    RETURN
       IF ( District = BLANK (), Project, District ), 
    
    
    CROSSFILTER ( 'Fact'[Project], Dim[Project], NONE )
)

[Jyaul1122]

@MFelix @v-saisrao-msft 

I can not create separate table for Geo as my data model is already overloaded.

[v-saisrao-msft]

Hi @Jyaul1122,

Have you had a chance to review the solution we shared by @MFelix? If the issue persists, feel free to reply so we can help further.

Thank you.

[MFelix · Accepted solution]

Hi @Jyaul1122 ,

Just to understand the model a little bit better so that you can have the correct approach:

• Geo column part of your Fact table?
• Do you have any dimension on your model that has the Geo reference?

Asking this because of the context of your calculation lets just pick up a simple table with the following data:

Geo column

Geo1

Geo2

Geo3

If I use this column in a visualization and also use it in a slicer the visualization will only show the values I select.

Lets assume I have select Geo1 on the slicer the visual will only show visual Geo1 and since they are the same column I'm not able to show a different value because the overall data that I have available for the visual is only that specific values Geo1 all other no matter how I try to pick them up will never show because they are not part of the data selected for the visual.

When you use a dimension table you will be able to change the context of the fact table because you can overwrite the filter context being passed trough the relationship betwen dimension and fact using the ALL, CROSSFILTER or other similar formulas.