Dynamic columns changing according to time intelligence

[Laxman222]

I have use case where  I need double headers such as MTD, YTD and under that I need to have sales,order count, margin and for YTD it shouldn't calculate order count. Any solution for this.

[Laxman222]

It worked for me

[OwenAuger · Accepted solution]

Hi @Laxman222 

Method 1: A general technique I've used in the past is:

1. Create a calculation group ("Measure") for measure selection (to allow measures to be "hidden")
2. 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".
3. 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