Forum Discussion
Dynamic columns changing according to time intelligence
- 1 year ago
Hi Laxman222
Method 1: A general technique I've used in the past is:
- Create a calculation group ("Measure") for measure selection (to allow measures to be "hidden")
- Create another calculation group ("Measure Visibility") with a calculation item that blanks out certain combinations of measure/time intelligence (in your case "Order Count"/"YTD".
- Apply the calculation item from step 2 as a filter on the visual.
Example PBIX attached for you to take a look.
Here is the DAX script for the Measure and Measure Visibility calculation groups in my example:
------------------------------- -- Calculation Group: 'Measure' ------------------------------- CALCULATIONGROUP 'Measure'[Measure] , Precedence = 1 CALCULATIONITEM "Sales" = [Sales] , FormatString = "#,0" , Ordinal = 0 CALCULATIONITEM "Margin" = [Margin] , FormatString = "#,0.00" , Ordinal = 1 CALCULATIONITEM "Order Count" = [Order Count] , FormatString = "#,0" , Ordinal = 1 ------------------------------------------ -- Calculation Group: 'Measure Visibility' ------------------------------------------ CALCULATIONGROUP 'Measure Visibility'[Measure Visibility] , Precedence = 2 CALCULATIONITEM "Exclude Order Count YTD" = VAR CurrentMeasure = SELECTEDVALUE ( 'Measure'[Measure] ) VAR CurrentTimeCalc = SELECTEDVALUE ( 'Time Intelligence'[Time Calc] ) VAR Result = IF ( NOT ( CurrentMeasure = "Order Count" && CurrentTimeCalc = "YTD" ), SELECTEDMEASURE () ) RETURN Result , FormatString = SELECTEDMEASUREFORMATSTRING ()Method 2: Another (possibly simpler) method is to use the Hierarchy Slicer custom visual to select the desired combinations of Time Intelligence and Measure calculation items. The Hierarchy Slicer will work if a blank measure is placed on it (or you could use another suitable measure).
Well these are a couple of possible approaches. Would something like these work in your case?
Regards
Hi Laxman222
Method 1: A general technique I've used in the past is:
- Create a calculation group ("Measure") for measure selection (to allow measures to be "hidden")
- Create another calculation group ("Measure Visibility") with a calculation item that blanks out certain combinations of measure/time intelligence (in your case "Order Count"/"YTD".
- Apply the calculation item from step 2 as a filter on the visual.
Example PBIX attached for you to take a look.
Here is the DAX script for the Measure and Measure Visibility calculation groups in my example:
-------------------------------
-- Calculation Group: 'Measure'
-------------------------------
CALCULATIONGROUP 'Measure'[Measure] , Precedence = 1
CALCULATIONITEM "Sales" = [Sales]
, FormatString = "#,0"
, Ordinal = 0
CALCULATIONITEM "Margin" = [Margin]
, FormatString = "#,0.00"
, Ordinal = 1
CALCULATIONITEM "Order Count" = [Order Count]
, FormatString = "#,0"
, Ordinal = 1
------------------------------------------
-- Calculation Group: 'Measure Visibility'
------------------------------------------
CALCULATIONGROUP 'Measure Visibility'[Measure Visibility] , Precedence = 2
CALCULATIONITEM "Exclude Order Count YTD" =
VAR CurrentMeasure =
SELECTEDVALUE ( 'Measure'[Measure] )
VAR CurrentTimeCalc =
SELECTEDVALUE ( 'Time Intelligence'[Time Calc] )
VAR Result =
IF (
NOT ( CurrentMeasure = "Order Count" && CurrentTimeCalc = "YTD" ),
SELECTEDMEASURE ()
)
RETURN
Result
, FormatString = SELECTEDMEASUREFORMATSTRING ()
Method 2: Another (possibly simpler) method is to use the Hierarchy Slicer custom visual to select the desired combinations of Time Intelligence and Measure calculation items. The Hierarchy Slicer will work if a blank measure is placed on it (or you could use another suitable measure).
Well these are a couple of possible approaches. Would something like these work in your case?
Regards