Simplifying a over complicated measure

[Anonymous]

Hi Experts

How would you simplify the following measure see attached note pad file. Not sure what they are trying to do by union data for each month.

https://www.dropbox.com/s/kqm97kw50os1zo7/union.txt?dl=0 

[AlB · Accepted solution]

Hi @Anonymous 

It's not a measure, it's a table. This kind of stuff is best done in PQ, though.  Here is a possible equivalent version , approx. 12x shorter:

Shorter version =
VAR auxCols_ =
    SELECTCOLUMNS (
        ADDCOLUMNS (
            GENERATESERIES ( 1, 12 ),
            "Month", FORMAT ( DATE ( 2020, [Value], [Value] ), "mmm" ),
            "Mth_No", [Value]
        ),
        "Month", [Month],
        "Month_No", [Mth_No]
    )
VAR baseT_ =
    DISTINCT (
        SELECTCOLUMNS (
            Consolidated_Financial_Reports,
            "Line_Item_Code", Consolidated_Financial_Reports[Line_Item_Code],
            "Line_Item_Description", Consolidated_Financial_Reports[Line_Item_Description],
            "Region", Consolidated_Financial_Reports[Region],
            "Channel", Consolidated_Financial_Reports[Channel],
            "Current_Year", Consolidated_Financial_Reports[C_Year],
            "Previous_Year", Consolidated_Financial_Reports[P_Year],
            "Current_Month", Consolidated_Financial_Reports[C_Month],
            "Acct_Type", "PM_Fcst",
            "CM_Value", Consolidated_Financial_Reports[Jan_CM_Fcst],
            "PM_Value", Consolidated_Financial_Reports[Jan_PM_Fcst],
            "LN_Sno", Consolidated_Financial_Reports[SNO],
            "BBP_Val", Consolidated_Financial_Reports[Jan_BBP],
            "PY_Actual", Consolidated_Financial_Reports[Jan_PY_Act],
            "CM_YTD", Consolidated_Financial_Reports[YTD_CM_Fcst],
            "PM_YTD", Consolidated_Financial_Reports[YTD_PM_Fcst],
            "BBP_YTD", Consolidated_Financial_Reports[YTD_BBP],
            "PY_Act_YTD", Consolidated_Financial_Reports[YTD_PY_Act],
            "CM_FY", Consolidated_Financial_Reports[FY_CM_Fcst],
            "PM_FY", Consolidated_Financial_Reports[FY_PM_Fcst],
            "BBP_FY", Consolidated_Financial_Reports[FY_BBP],
            "PY_Act_FY", Consolidated_Financial_Reports[FY_PY_Act],
            "CM_Q1", Consolidated_Financial_Reports[Q1_CM_Fcst],
            "CM_Q2", Consolidated_Financial_Reports[Q2_CM_Fcst],
            "CM_Q3", Consolidated_Financial_Reports[Q3_CM_Fcst],
            "CM_Q4", Consolidated_Financial_Reports[Q4_CM_Fcst],
            "PM_Q1", Consolidated_Financial_Reports[Q1_PM_Fcst],
            "PM_Q2", Consolidated_Financial_Reports[Q2_PM_Fcst],
            "PM_Q3", Consolidated_Financial_Reports[Q3_PM_Fcst],
            "PM_Q4", Consolidated_Financial_Reports[Q4_PM_Fcst],
            "BBP_Q1", Consolidated_Financial_Reports[Q1_BBP],
            "BBP_Q2", Consolidated_Financial_Reports[Q2_BBP],
            "BBP_Q3", Consolidated_Financial_Reports[Q3_BBP],
            "BBP_Q4", Consolidated_Financial_Reports[Q4_BBP],
            "PY_Act_Q1", Consolidated_Financial_Reports[Q1_PY_Act],
            "PY_Act_Q2", Consolidated_Financial_Reports[Q2_PY_Act],
            "PY_Act_Q3", Consolidated_Financial_Reports[Q3_PY_Act],
            "PY_Act_Q4", Consolidated_Financial_Reports[Q4_PY_Act]
        )
    )
RETURN
    CROSSJOIN ( baseT_, auxCols_ )

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

Many thanks for excellent feedback