Forum Discussion
Create a new table from existing table (Using DAX Commands)
Hello my saviours, Hope you are all good. I need to create a new table based on calculation from Table 1 My table looks like this Table 1: IDNO Store_Number Slow AA1 S1 y AA1 S2 n...
Anonymous
It can actually be done in a far, far easier way if you have a column that specifies order in Table1 (or add an index as suggested by nandic). In my previous take, I somehow (must have been asleep) assumed you wanted a solution that would use the "Affected by" column in "Table1 (updated)". This only overcomplicates things unnecessarily
Table2 = GENERATE ( Table1, SELECTCOLUMNS ( CALCULATETABLE ( DISTINCT ( Table1[Store_Number] ), Table1[Index] < EARLIER ( Table1[Index] ), Table1[Slow] = "y", ALLEXCEPT ( Table1, Table1[IDNO] ) ), "Affected by", Table1[Store_Number] ) )Please mark the question solved when done and consider giving kudos if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
A follow up question here: in PowerBI I have a (complex) DAX query that works great within the DAX query view, and returns a table .
Now I want to use the (complex) query definition to define a table.... yet it fails. Is there any smart way?
Hello Community! I Am trying to use a DAX query to create a table... My query works fine in the DAX editor... but I fail to transpose it as a table DAX-definition.
DAX output is:
| SERIES | ACTIVITY | TCO | SERIES_VALUE |
| SpecCost | Clean Energy | 48.5 | 0 |
| SpecCost | Increase Recycling | 683 | 0 |
| SpecCost | Convert PDD | 84.5 | 1.77 |
I would like to have this as a table.... Any idea how to proceed ? 🙂 Thanks & KR, Alfred
DAX query view code:
// DAX Query
DEFINE
COLUMN '__SQDS0VisualCalcs'[Percent of grand total] = ( BLANK() )
COLUMN '__SQDS0VisualCalcs'[Percent of grand total 1] = ( BLANK() )
VAR __SQDS0FilterTable = TREATAS({"2030"}, 'Q6_i6b_ABAT_sort'[YEAR_STREAM])
VAR __SQDS0FilterTable2 = TREATAS({"Ghirardelli Chocolate Company"}, 'Q6_i6b_ABAT_sort'[SITE])
VAR __SQDS0FilterTable3 =
FILTER(
KEEPFILTERS(VALUES('Q6_i6b_ABAT_sort'[SCOPE_SBT_GROUP_FLAG_TYPE])),
NOT('Q6_i6b_ABAT_sort'[SCOPE_SBT_GROUP_FLAG_TYPE] IN {"Scope ALL"})
)
VAR __ValueFilterDM2 =
FILTER(
KEEPFILTERS(
SUMMARIZECOLUMNS(
'Q6_i6b_ABAT_sort'[Index],
'Q6_i6b_ABAT_sort'[Action name],
__SQDS0FilterTable,
__SQDS0FilterTable2,
__SQDS0FilterTable3,
"SumSpecCost_kCHF_tCO2e", CALCULATE(SUM('Q6_i6b_ABAT_sort'[SpecCost_kCHF_tCO2e])),
"SumtCO2e", CALCULATE(SUM('Q6_i6b_ABAT_sort'[tCO2e])),
"meas_MAX", 'Q6_i6b_ABAT_sort'[meas_MAX],
"meas_COMPL", 'Q6_i6b_ABAT_sort'[meas_COMPL]
)
),
[SumSpecCost_kCHF_tCO2e] >= 0
)
VAR __SQDS0Core =
SUMMARIZECOLUMNS(
ROLLUPADDISSUBTOTAL(
'Q6_i6b_ABAT_sort'[Index], "IsSQDS0GrandTotalRowTotal",
'Q6_i6b_ABAT_sort'[Action name], "IsDM0Total"
),
__SQDS0FilterTable,
__SQDS0FilterTable2,
__SQDS0FilterTable3,
__ValueFilterDM2,
"SumSpecCost_kCHF_tCO2e", CALCULATE(SUM('Q6_i6b_ABAT_sort'[SpecCost_kCHF_tCO2e])),
"SumtCO2e", CALCULATE(SUM('Q6_i6b_ABAT_sort'[tCO2e])),
"meas_MAX", 'Q6_i6b_ABAT_sort'[meas_MAX],
"meas_COMPL", 'Q6_i6b_ABAT_sort'[meas_COMPL]
)
VAR __SQDS0VisualCalcsInput =
SELECTCOLUMNS(
KEEPFILTERS(
SELECTCOLUMNS(
__SQDS0Core,
"Index", 'Q6_i6b_ABAT_sort'[Index],
"IsSQDS0GrandTotalRowTotal", [IsSQDS0GrandTotalRowTotal],
"Action_name", 'Q6_i6b_ABAT_sort'[Action name],
"IsDM0Total", [IsDM0Total],
"SumSpecCost_kCHF_tCO2e", [SumSpecCost_kCHF_tCO2e],
"SumtCO2e", [SumtCO2e],
"meas_MAX", [meas_MAX],
"meas_COMPL", [meas_COMPL]
)
),
"Sum of Index", [Index],
"Action name", [Action_name],
"IsSQDS0GrandTotalRowTotal", [IsSQDS0GrandTotalRowTotal],
"IsDM0Total", [IsDM0Total],
"SpecCost_kCHF_tCO2e", [SumSpecCost_kCHF_tCO2e],
"tCO2e", [SumtCO2e],
"meas_MAX", [meas_MAX],
"meas_COMPL", [meas_COMPL]
)
TABLE '__SQDS0VisualCalcs' =
__SQDS0VisualCalcsInput
WITH VISUAL SHAPE
AXIS rows
GROUP [Sum of Index] TOTAL [IsSQDS0GrandTotalRowTotal]
GROUP [Action name] TOTAL [IsDM0Total]
ORDER BY [Sum of Index] ASC, [Action name] ASC DENSIFY "IsDensifiedRow"
VAR __SQDS0RemoveEmptyDensified =
FILTER(
KEEPFILTERS('__SQDS0VisualCalcs'),
OR( NOT('__SQDS0VisualCalcs'[IsDensifiedRow]), NOT(ISBLANK('__SQDS0VisualCalcs'[Percent of grand total])) )
)
VAR __DS0Core =
SELECTCOLUMNS(
KEEPFILTERS(
FILTER(
KEEPFILTERS(__SQDS0RemoveEmptyDensified),
AND(
'__SQDS0VisualCalcs'[IsSQDS0GrandTotalRowTotal] = FALSE,
'__SQDS0VisualCalcs'[IsDM0Total] = FALSE
)
)
),
"'__SQDS0VisualCalcs'[Sum of Index]", '__SQDS0VisualCalcs'[Sum of Index],
"'__SQDS0VisualCalcs'[Action name]", '__SQDS0VisualCalcs'[Action name],
"'__SQDS0VisualCalcs'[SpecCost_kCHF_tCO2e]", '__SQDS0VisualCalcs'[SpecCost_kCHF_tCO2e],
"'__SQDS0VisualCalcs'[tCO2e]", '__SQDS0VisualCalcs'[tCO2e],
"'__SQDS0VisualCalcs'[Percent of grand total]", '__SQDS0VisualCalcs'[Percent of grand total],
"'__SQDS0VisualCalcs'[Percent of grand total 1]", '__SQDS0VisualCalcs'[Percent of grand total 1],
"'__SQDS0VisualCalcs'[meas_MAX]", '__SQDS0VisualCalcs'[meas_MAX],
"'__SQDS0VisualCalcs'[meas_COMPL]", '__SQDS0VisualCalcs'[meas_COMPL]
)
VAR __DS0RemoveContextOnlyColumns = UNION(
SELECTCOLUMNS(
KEEPFILTERS(__DS0Core),
"Series","SpecCost",
"Action name", '__SQDS0VisualCalcs'[Action name],
"tCO2e", '__SQDS0VisualCalcs'[tCO2e],
"SeriesVALUE", '__SQDS0VisualCalcs'[SpecCost_kCHF_tCO2e]
),
SELECTCOLUMNS(
KEEPFILTERS(__DS0Core),
"Series","SpecCost_COMPL",
"Action name", '__SQDS0VisualCalcs'[Action name],
"tCO2e", '__SQDS0VisualCalcs'[tCO2e],
"SeriesVALUE", '__SQDS0VisualCalcs'[meas_COMPL]
)
)
VAR OUTPUT = TOPN(501, __DS0RemoveContextOnlyColumns, 1 )
EVALUATE
OUTPUT