Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
First time poster here, I have run into a problem
I have a table with an asset ID Number, and inspection dates (Direct Query) we call this the inspection table
I then have another (Generated) table that with the same asset ID Numbers that pulls data from various other sources to such as order numbers etc
In my generated table I have columns with first and last inspection dates, which I am then trying to return the Inspector Name with lookupvalue using Asset ID and First Inspection date without success.
Any help would be appreciated
Thanks
Hi All,
I am still looking for a solution on this can anyone help...?
@daxdummy , In direct query mode, you will not be able to move data from one table to another.
If I am being ignorant I apologise, but why not?
I am already able to get the first and last inspection dates from the direct query table into my other table
@daxdummy , related will work in Dax in direct query, but I doubt lookup. Yes in Power query you can try merge.
If your other table is created in power BI, then most of the thing should work.
Ways to copy in dax
//Only one to many
Item Name = RELATED('item'[Brand])
//Copied from city to sales. Join of your choice
City Name = maxx(FILTER(geography,geography[City Id]=Sales[City Id]),geography[City])
Lookup
Month Name = LOOKUPVALUE('date'[Month Year],'date'[Date],Sales[Sales Date])
Power Query
https://radacad.com/append-vs-merge-in-power-bi-and-power-query
Thanks for the reply @amitchandak
I have tried your middle option, however I still get the error
The resultset of a query to external data source has exceeded the maximum allowed size of '1000000' rows.
I'm under the impression if we apply the filters in the calculation then this should filter the table to a single row, is this not correct?
Thanks Mariusz,
I am already able to get the date I want to add the inspector name to my table based on 'Asset ID' & and the 'Inspection Date'
Hi @daxdummy
Try using Power Query, Group By Asset ID and aggregate dates for min ispection dates and later mearge queries on this two fields to retrive the name.
on most data sorces like SQL native query is supported so it should work.
Here is the DAX I am using
Hi @daxdummy ,
If over 1 million rows are returned from DirectQuery, Power BI returns an error (unless in Premium capacity, and the row count is under the admin-set limit).
You can use SQL statement preprocessing in advanced options when connecting data.
The same thing can be done in the query editor using M query.
For example
(Year as number)as table=>
let
Source = Sql.Database("******", "test", [Query="SELECT * FROM test_11#(lf) WHERE Year IN ("&Number.ToText(Year)&")", CreateNavigationProperties=false])
in
Source
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
98 | |
90 | |
84 | |
70 | |
66 |
User | Count |
---|---|
113 | |
104 | |
101 | |
73 | |
65 |