Hi!
Having some troubles with running a query against a dataset. The DAX query returned in Power BI is the one below, however, it's only returning the Date columns (label and sort).
// DAX Query
DEFINE
VAR __DS0FilterTable =
TREATAS({"Show"}, '_Timeline'[_Displayed Ms])
VAR __DS0FilterTable2 =
FILTER(
KEEPFILTERS(VALUES('Company'[type])),
NOT('Company'[type] IN {"PARTNER"})
)
VAR __DS0Core =
SUMMARIZECOLUMNS(
ROLLUPADDISSUBTOTAL(
ROLLUPGROUP('_Deal'[_Forecast Category], '_Deal'[_Forecast Sort]), "IsGrandTotalRowTotal",
'Company'[CompanyName], "IsDM1Total"
),
'_Timeline'[_Date],
'_Timeline'[_Date Sort],
__DS0FilterTable,
__DS0FilterTable2,
"v_Renewal_ARR_disp", '_Deal'[_Renewal ARR disp],
"Minv_Forecast_Category", IGNORE(CALCULATE(MIN('_Deal'[_Forecast Category])))
)
VAR __DS0PrimaryWindowed =
TOPN(
102,
SUMMARIZE(
__DS0Core,
'_Deal'[_Forecast Category],
'_Deal'[_Forecast Sort],
[IsGrandTotalRowTotal],
'Company'[CompanyName],
[IsDM1Total]
),
[IsGrandTotalRowTotal],
0,
'_Deal'[_Forecast Sort],
1,
'_Deal'[_Forecast Category],
1,
[IsDM1Total],
0,
'Company'[CompanyName],
1
)
VAR __DS0SecondaryBase =
SUMMARIZE(__DS0Core, '_Timeline'[_Date], '_Timeline'[_Date Sort])
VAR __DS0SecondaryShowAll =
ADDMISSINGITEMS(
'_Timeline'[_Date],
'_Timeline'[_Date Sort],
__DS0SecondaryBase,
'_Timeline'[_Date],
'_Timeline'[_Date Sort],
__DS0FilterTable,
__DS0FilterTable2
)
VAR __DS0Secondary =
TOPN(102, __DS0SecondaryShowAll, '_Timeline'[_Date Sort], 1, '_Timeline'[_Date], 1)
VAR __DS0BodyLimited =
NATURALLEFTOUTERJOIN(
__DS0PrimaryWindowed,
SUBSTITUTEWITHINDEX(
__DS0Core,
"ColumnIndex",
__DS0Secondary,
'_Timeline'[_Date Sort],
ASC,
'_Timeline'[_Date],
ASC
)
)
EVALUATE
__DS0Secondary
ORDER BY
'_Timeline'[_Date Sort], '_Timeline'[_Date]
EVALUATE
__DS0BodyLimited
ORDER BY
[IsGrandTotalRowTotal] DESC,
'_Deal'[_Forecast Sort],
'_Deal'[_Forecast Category],
[IsDM1Total] DESC,
'Company'[CompanyName],
[ColumnIndex]
Basically I'd need the raw date out of this table (so what I'm missing is precisely the 'expand' rows portion, i.e. forecast category, the customer names and respective values, cut out in black). Do I need to further define the fields in the query? I've run similar ones and the results are ok. Sorry if I haven't been clear enough, I'm a beginner with Power Automate.
