Forum Discussion
Helper IIIGet RAG by Project but always keep one extra row for dedicated Project
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
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.
7 Replies
- MFelix
Super User
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?
- 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 ) )
v-saisrao-msftCommunity Support
