Forum Discussion

Analitika's avatar
Analitika
Post Prodigy
6 years ago
Solved

Measure simplifier and compress

I have a lot of terrible and complex measure from other projects.

Is there are any way to simplify and compress such measures like:

 

VAR valText1 = "Text1"
VAR valText12 = "Text2"
VAR subt =
    CALCULATE (
        CALCULATE (
            SUM ( 'Table'[Sum] ),
            FILTER (
                'Table',
                'Table'[Type1] IN { 0 }
                    && NOT 'Table'[Type2] = 2
                    && NOT 'Table'[Code] IN { valText1, valText12 }
            )
        ),
        FILTER ( ALL ( 'Date'[Date] ), 'Date'[Date] <= MAX ( 'Date'[Date] ) )
    )
        - CALCULATE (
            CALCULATE (
                SUM ( 'Table'[Sum] ),
                FILTER (
                    'Table',
                    'Table'[Type1] IN { 0 }
                        && 'Table'[Type2] = 2
                        && NOT 'Table'[Code] IN { valText1, valText12 }
                )
            ),
            FILTER ( ALL ( 'Date'[Date] ), 'Date'[Date] <= MAX ( 'Date'[Date] ) )
        )
        - CALCULATE (
            CALCULATE (
                SUM ( 'Table'[Sum] ),
                FILTER (
                    'Table',
                    'Table'[Type1] IN { 0 }
                        && 'Table'[Code] IN { valText1, valText12 }
                        && 'Table'[Sign] > 0
                )
            ),
            FILTER ( ALL ( 'Date'[Date] ), 'Date'[Date] <= MAX ( 'Date'[Date] ) )
        )
        - CALCULATE (
            CALCULATE (
                SUM ( 'Table'[Sum] ),
                FILTER (
                    'Table',
                    'Table'[Type1] IN { 0 }
                        && 'Table'[Code] IN { valText1, valText12 }
                        && 'Table'[Sign] < 0
                )
            ),
            FILTER ( ALL ( 'Date'[Date] ), 'Date'[Date] <= MAX ( 'Date'[Date] ) )
        )
        + - CALCULATE (
            CALCULATE (
                SUM ( 'Table'[Sum] ),
                FILTER ( 'Table', 'Table'[Type1] IN { 2 } )
            ),
            FILTER ( ALL ( 'Date'[Date] ), 'Date'[Date] <= MAX ( 'Date'[Date] ) )
        )
RETURN
    SWITCH(TRUE(),
        "505s" in distinct ( 'Table_2'[Column1] ) ,
            - subt,
        "948r" in distinct ( 'Table_2'[Column1] ),
            subt
    )
Need Help
Tips and Tricks
  • AntrikshSharma's avatar
    AntrikshSharma
    6 years ago

    Try this:

     

    col =
VAR valText1 = "Text1"
VAR valText12 = "Text2"
VAR ListOfDate =
    FILTER ( ALL ( 'Date'[Date] ), 'Date'[Date] <= MAX ( 'Date'[Date] ) )
VAR F =
    FILTER (
        'Table',
        'Table'[Type1] IN { 0 }
            && NOT 'Table'[Code] IN { valText1, valText12 }
    )
VAR subt1 =
    CALCULATE (
        SUM ( 'Table'[Sum] ),
        F,
        KEEPFILTERS ( 'Table'[Type2] <> 2 ),
        ListOfDate
    )
VAR Subt2 =
    CALCULATE (
        SUM ( 'Table'[Sum] ),
        F,
        KEEPFILTERS ( 'Table'[Type2] = 2 ),
        ListOfDate
    )
VAR Subt3 =
    CALCULATE (
        SUM ( 'Table'[Sum] ),
        F,
        KEEPFILTERS ( 'Table'[Sign] > 0 ),
        ListOfDate
    )
VAR Subt4 =
    CALCULATE (
        SUM ( 'Table'[Sum] ),
        F,
        KEEPFILTERS ( 'Table'[Sign] < 0 ),
        ListOfDate
    )
VAR Subt5 =
    CALCULATE (
        SUM ( 'Table'[Sum] ),
        FILTER ( 'Table', 'Table'[Type1] IN { 2 } ),
        ListOfDate
    )
VAR Result = subt1 - Subt2 - Subt3 - Subt4 - Subt5
RETURN
    SWITCH (
        TRUE (),
        "505s" IN DISTINCT ( 'Table_2'[Column1] ), - Result,
        "948r" IN DISTINCT ( 'Table_2'[Column1] ), Result
    )

2 Replies

  • AntrikshSharma's avatar
    AntrikshSharma
    Community Champion
    6 years ago

    Try this:

     

    col =
VAR valText1 = "Text1"
VAR valText12 = "Text2"
VAR ListOfDate =
    FILTER ( ALL ( 'Date'[Date] ), 'Date'[Date] <= MAX ( 'Date'[Date] ) )
VAR F =
    FILTER (
        'Table',
        'Table'[Type1] IN { 0 }
            && NOT 'Table'[Code] IN { valText1, valText12 }
    )
VAR subt1 =
    CALCULATE (
        SUM ( 'Table'[Sum] ),
        F,
        KEEPFILTERS ( 'Table'[Type2] <> 2 ),
        ListOfDate
    )
VAR Subt2 =
    CALCULATE (
        SUM ( 'Table'[Sum] ),
        F,
        KEEPFILTERS ( 'Table'[Type2] = 2 ),
        ListOfDate
    )
VAR Subt3 =
    CALCULATE (
        SUM ( 'Table'[Sum] ),
        F,
        KEEPFILTERS ( 'Table'[Sign] > 0 ),
        ListOfDate
    )
VAR Subt4 =
    CALCULATE (
        SUM ( 'Table'[Sum] ),
        F,
        KEEPFILTERS ( 'Table'[Sign] < 0 ),
        ListOfDate
    )
VAR Subt5 =
    CALCULATE (
        SUM ( 'Table'[Sum] ),
        FILTER ( 'Table', 'Table'[Type1] IN { 2 } ),
        ListOfDate
    )
VAR Result = subt1 - Subt2 - Subt3 - Subt4 - Subt5
RETURN
    SWITCH (
        TRUE (),
        "505s" IN DISTINCT ( 'Table_2'[Column1] ), - Result,
        "948r" IN DISTINCT ( 'Table_2'[Column1] ), Result
    )