Forum Discussion
Column values cannot be used as values in table or graph
Hello all,
I'm very new to Power BI, I opened it for the first time yesterday, but I have some background with SQL queries. I'm trying to accomplish smething very simple, a graph that shows "Power on time" vs "Tests execution time".
My data comes from a MySQL table, with two tables (simplified from real tables) :
power_on : timestamp, power_on_time, test_bench_id
test_execution : timestamp, test_execution_time, test_id, test_bench_id
Since there can be multiple tests performed on the same day, I created an intermediate table, that sums the execution_time from different tests happening on the same day. Since there can be multiple entries in the power_on table for the same day, I also created an intermediate table that sums the power_on_time for each different day. At the end I have the two intermediate tables :
power_on_grouped : date, power_on_time, test_bench_id
test_execution_grouped : date, test_execution_time, test_bench_id
I merge these two tables ino one with a multi-column join to finally have the following table :
power_on_vs_test_execution : date, power_on_time, test_execution_time, test_bench_id
And when I try to make a table or a graph chart using that last table, I'm unable to select power_on_time and execution_time as values.
I'm using Power BI Desktop 2.132.1053.0 64-bit. I've searched a bit and found some stuff about "Discourage implicit measures" and Tabular Editor, but being on a PC without admin privilege, I can't install that software. Is there any way around that ?
I've tried deleting all the measures in my model without success.
The request to create power_on_grouped :
let
Source = power_on,
#"Colonnes supprimées" = Table.RemoveColumns(Source,{"STATION_NUM"}),
#"Date extraite" = Table.TransformColumns(#"Colonnes supprimées",{{"TIMESTAMP", DateTime.Date, type date}}),
#"Lignes groupées" = Table.Group(#"Date extraite", {"station_feature.STATION_ID", "TIMESTAMP"}, {{"Uptime", each List.Sum([OI_UPTIME]), type number}}),
#"Colonnes renommées" = Table.RenameColumns(#"Lignes groupées",{{"station_feature.STATION_ID", "test_bench_id"}, {"TIMESTAMP", "date"}, {"Uptime", "power_on_time"}})
in
#"Colonnes renommées"
test_execution_grouped
let
Source = test_execution,
#"Lignes groupées" = Table.Group(Source, {"station_feature.STATION_ID", "TIMESTAMP"}, {{"Execution time by ODTGen", each List.Sum([EXECUTION_TIME]), type nullable number}, {"Timestamp.1", each _, type table [TIMESTAMP=datetime, ID=text, STATION_NUM=nullable text, SOFTVER_ODTGEN=nullable text, HARDVER_ODTGEN=nullable text, NEXT_CAL_DATE=nullable date, UUT_NAME=nullable text, UUT_SERIAL_NUMBER=nullable text, UUT_VERSION=nullable text, USER_LOGIN_NAME=nullable text, USER_LOGIN_LOGIN=text, START_DATE_TIME=nullable datetime, EXECUTION_TIME=nullable number, UUT_STATUS=nullable text, UUT_ERROR_CODE=nullable number, UUT_ERROR_MESSAGE=nullable text, PAT_NAME=text, PAT_VERSION=text, TEST_LEVEL=nullable text, INTERFACE_ID=number, EXECUTION_MODE=nullable text, LOOP_MODE=nullable text, STOP_ON_FAIL=number, EXECUTION_GLOBAL_INFOS=nullable text, EXECUTION_COMMENT=nullable text, VOLTAGE_CHOICE=nullable number, VOLTAGE_MIN=nullable number, VOLTAGE_NOM=nullable number, VOLTAGE_MAX=nullable number, station_feature.STATION_ID=nullable text, station_feature.SITE=nullable text, Duration=duration]}}),
#"Date extraite" = Table.TransformColumns(#"Lignes groupées",{{"TIMESTAMP", DateTime.Date, type date}}),
#"Colonnes supprimées" = Table.RemoveColumns(#"Date extraite",{"Timestamp.1"}),
#"Lignes groupées1" = Table.Group(#"Colonnes supprimées", {"station_feature.STATION_ID", "TIMESTAMP"}, {{"Execution time by day", each List.Sum([Execution time by ODTGen]), type nullable number}}),
#"Colonnes renommées" = Table.RenameColumns(#"Lignes groupées1",{{"station_feature.STATION_ID", "test_bench_id"}, {"TIMESTAMP", "date"}, {"Execution time by day", "test_execution_time"}})
in
#"Colonnes renommées"
And the final table :
let
Source = power_on_grouped,
#"Requêtes fusionnées" = Table.NestedJoin(Source, {"date", "test_bench_id"}, test_execution_grouped, {"date", "test_bench_id"}, "test_execution_time", JoinKind.Inner),
#"test_execution_time développé" = Table.ExpandTableColumn(#"Requêtes fusionnées", "test_execution_time", {"test_execution_time"}, {"test_execution_time"})
in
#"test_execution_time développé"
The table I obtain in the table view (opposed to report view) shows that I have all the data that I want, but I just can't put it on a report, that's very frustrating !
Thanks for the help !
Hi Anonymous ,
Thanks for the help ! I've tried a little bit more yesterday and I found the disabled implicit measure button in the Power BI Desktop interface (though I can't seem to find it today), played a bit with it and it solved my problem ! I will post if I find it back today.
Edit : found it :
2 Replies
- AnonymousNot applicable
Hi Loufylouf ,
I noticed that you do the merge queries in Power Query, right?
Did you expand the table in the column after merging? If not, the data in tables will not be displayed in Desktop.
Reference: Expand or aggregate the new merged table column
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly - LoufyloufFrequent Visitor
Hi Anonymous ,
Thanks for the help ! I've tried a little bit more yesterday and I found the disabled implicit measure button in the Power BI Desktop interface (though I can't seem to find it today), played a bit with it and it solved my problem ! I will post if I find it back today.
Edit : found it :