Hi,
I am attempting to create a visualization in Power of 'users per time and day' by using data from a Redshift database but am getting an error message which reads:
"OLE DB or ODBC error: [Expression.Error] We couldn't fold the expression to the data source. "
I am using one date table (called d_date) which contains one date per row and associated information about the date. A row could look like this:
date, month, day, year, day of week, day name, month name, quarter, week
2019-02-07, 2, 7, 2019, 4, Thursday, February, 1, 6
The 'day name' column has been sorted by 'day of week' in Power BI.
The other table (called ga_seo_general) contains a number of columns and a row could look like this:
date, hour, users, [...]
2019-02-07, 5, 13, [...]
I have created a relationship between the 'date' columns in each table. Next step is to create a matrix visualization in Power BI using ga_seo_general[users] as value, ga_seo_general[hour] as column and d_date[day of week] as rows. This gives my a two dimensional table of how many users are visiting a certain site per day of week and hour of day. However, when trying to switch from d_date[day of week] to d_date[day name] as row entry, I get the error message as mentioned in the beginning of my post.
I have managed to trace the internal queries for these two examples and can verify that the SQL queries to Redshift are working just fin (I can run the same queries in Redshift and get the correct response). But I am not so sure about these DAX measures:
This DAX measure works fine (using d_date[day of week]😞
DEFINE
VAR __DS0Core =
SUMMARIZECOLUMNS(
ROLLUPADDISSUBTOTAL('ga_seo_general'[hour], "IsGrandTotalRowTotal"),
ROLLUPADDISSUBTOTAL('d_date'[day of week], "IsGrandTotalColumnTotal"),
"Sumusers", CALCULATE(SUM('ga_seo_general'[users]))
)
VAR __DS0Primary =
TOPN(
102,
SUMMARIZE(__DS0Core, 'ga_seo_general'[hour], [IsGrandTotalRowTotal]),
[IsGrandTotalRowTotal],
0,
'ga_seo_general'[hour],
1
)
VAR __DS0Secondary =
TOPN(
102,
SUMMARIZE(__DS0Core, 'd_date'[day of week], [IsGrandTotalColumnTotal]),
[IsGrandTotalColumnTotal],
1,
'd_date'[day of week],
1
)
VAR __DS0CoreNoInstanceFiltersNoTotals =
FILTER(
KEEPFILTERS(__DS0Core),
AND([IsGrandTotalRowTotal] = FALSE, [IsGrandTotalColumnTotal] = FALSE)
)
EVALUATE
GROUPBY(
__DS0CoreNoInstanceFiltersNoTotals,
"MinSumusers", MINX(CURRENTGROUP(), [Sumusers]),
"MaxSumusers", MAXX(CURRENTGROUP(), [Sumusers])
)
EVALUATE
__DS0Secondary
ORDER BY
[IsGrandTotalColumnTotal], 'd_date'[day of week]
EVALUATE
NATURALLEFTOUTERJOIN(
__DS0Primary,
SUBSTITUTEWITHINDEX(
__DS0Core,
"ColumnIndex",
__DS0Secondary,
[IsGrandTotalColumnTotal],
ASC,
'd_date'[day of week],
ASC
)
)
ORDER BY
[IsGrandTotalRowTotal] DESC, 'ga_seo_general'[hour], [ColumnIndex]
But this DAX measure (using d_date[day name]) yields an error:
DEFINE
VAR __DS0Core =
SUMMARIZECOLUMNS(
ROLLUPADDISSUBTOTAL('ga_seo_general'[hour], "IsGrandTotalRowTotal"),
ROLLUPADDISSUBTOTAL('d_date'[day name], "IsGrandTotalColumnTotal"),
"Sumusers", CALCULATE(SUM('ga_seo_general'[users]))
)
VAR __DS0Primary =
TOPN(
102,
SUMMARIZE(__DS0Core, 'ga_seo_general'[hour], [IsGrandTotalRowTotal]),
[IsGrandTotalRowTotal],
0,
'ga_seo_general'[hour],
1
)
VAR __DS0Secondary =
TOPN(
102,
SUMMARIZE(__DS0Core, 'd_date'[day name], [IsGrandTotalColumnTotal]),
[IsGrandTotalColumnTotal],
1,
'd_date'[day name],
1
)
VAR __DS0CoreNoInstanceFiltersNoTotals =
FILTER(
KEEPFILTERS(__DS0Core),
AND([IsGrandTotalRowTotal] = FALSE, [IsGrandTotalColumnTotal] = FALSE)
)
EVALUATE
GROUPBY(
__DS0CoreNoInstanceFiltersNoTotals,
"MinSumusers", MINX(CURRENTGROUP(), [Sumusers]),
"MaxSumusers", MAXX(CURRENTGROUP(), [Sumusers])
)
EVALUATE
__DS0Secondary
ORDER BY
[IsGrandTotalColumnTotal], 'd_date'[day name]
EVALUATE
NATURALLEFTOUTERJOIN(
__DS0Primary,
SUBSTITUTEWITHINDEX(
__DS0Core,
"ColumnIndex",
__DS0Secondary,
[IsGrandTotalColumnTotal],
ASC,
'd_date'[day name],
ASC
)
)
ORDER BY
[IsGrandTotalRowTotal] DESC, 'ga_seo_general'[hour], [ColumnIndex]I would appreciate some insight for you and am looking forward to a response. Let me know if you need further information.
Kind regards,
John
Feedback Type:
Frown (Error)
Timestamp:
2019-02-12T14:05:45.0862585Z
Local Time:
2019-02-12T15:05:45.0862585+01:00
Session ID:
751cb272-e77f-4017-9ac0-771fe397d88c
Release:
December 2018
Product Version:
2.65.5313.1381 (18.12) (x64)
Error Message:
OLE DB- eller ODBC-fel: [Expression.Error] Vi kunde inte vika uttrycket för datakällan. Försök med ett enklare uttryck..
OS Version:
Microsoft Windows NT 10.0.17134.0 (x64 sv-SE)
CLR Version:
4.7 or later [Release Number = 461808]
Peak Virtual Memory:
38.9 GB
Private Memory:
960 MB
Peak Working Set:
1.01 GB
IE Version:
11.523.17134.0
User ID:
34b8ad87-88c4-4574-90b9-a89ab92c8636
Workbook Package Info:
1* - sv-SE, Query Groups: 0, fastCombine: Disabled, runBackgroundAnalysis: True.
Telemetry Enabled:
True
Model Default Mode:
DirectQuery
Snapshot Trace Logs:
C:\Users\john.isaksson\Microsoft\Power BI Desktop Store App\FrownSnapShot197881854.zip
Performance Trace Logs:
C:\Users\john.isaksson\Microsoft\Power BI Desktop Store App\PerformanceTraces.zip
Enabled Preview Features:
PBI_shapeMapVisualEnabled
PBI_variationUIChange
Disabled Preview Features:
MIntellisense
PBI_SpanishLinguisticsEnabled
PBI_PdfImport
PBI_ColumnProfiling
PBI_PythonSupportEnabled
PBI_showIncrementalRefreshPolicy
PBI_showManageAggregations
PBI_FuzzyMatching
PBI_EnableWebDiagramView
PBI_improvedFilterExperience
PBI_qnaLiveConnect
Disabled DirectQuery Options:
PBI_DirectQuery_Unrestricted
TreatHanaAsRelationalSource
Cloud:
GlobalCloud
DPI Scale:
125%
Supported Services:
Power BI
Formulas:
section Section1;
shared ga_seo_general = let
Källa = AmazonRedshift.Database("skf-web-analytics.c6okg9pna554.eu-west-1.redshift.amazonaws.com:5439", "prod", null),
us = Källa{[Name="us"]}[Data],
ga_seo_general1 = us{[Name="ga_seo_general"]}[Data]
in
ga_seo_general1;
shared d_date = let
Källa = AmazonRedshift.Database("skf-web-analytics.c6okg9pna554.eu-west-1.redshift.amazonaws.com:5439", "prod", null),
utilities = Källa{[Name="utilities"]}[Data],
d_date1 = utilities{[Name="d_date"]}[Data],
#"Sorterade rader" = Table.Sort(d_date1,{{"date", Order.Descending}})
in
#"Sorterade rader";
v-yetao1-msft
on:
Admin Monitoring Workspace - Dataset has removed f...
on:
Get External Data: Power BI semantic models throws...
Idrissshatila
on:
Timeline slicer is showing 30 & 31 March under 30 ...