Return a string from another table filtered on string and date

[daxdummy]

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

[daxdummy]

Hi All,

I am still looking for a solution on this can anyone help...?

[amitchandak]

@daxdummy , In direct query mode, you will not be able to move data from one table to another.

[daxdummy]

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

[amitchandak]

@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

[daxdummy]

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?

[Mariusz]

Hi @daxdummy 

You can try CALCULATE( MIN( date )  )  provided you have a relationship between this tables.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[daxdummy]

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'

[Mariusz]

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.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[daxdummy]

Here is the DAX I am using

Inspector = LOOKUPVALUE(Inspection_Tbl[Inspector],Inspection_Tbl[Asset ID],'My Table'[Asset ID],Inspection_Tbl[InsDate],'My Table'[First Inspection Date],BLANK())

I am getting the error

'The resultset of a query to external data source has exceeded the maximum allowed size of '1000000' rows.'

I have also tried CALCULATE filtering on the First Inspection Date & Asset ID but asking for 'MAX','MIN' etc which I would assume doesn't apply to a string value. 

Result should be a single string Inspector Name

[V-lianl-msft]

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.