Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
Many thanks for excellent feedback
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |