Forum Discussion
Helper IIIGet RAG by Project but always keep one extra row for dedicated Project
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.
Super UserHi 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?
Helper III
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
I can not create separate table for Geo as my data model is already overloaded.
- MFelix
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.