How to make DAX based on Query and Excel table

[g_georgiev444]

Hello,

I have a Direct query as a data source and an Excel table with 3 columns. I match the 2 based on Query1.Destination and Sheet1.Column1

This is the Query1.destination column results:

They are the same as what Sheet1.Column 1 have. The only possible relationship is 1:many which PowerBI allows.

I create a new column in Sheet1 in which I need to put the following logic: IF query1.destination IS EMPTY or NULL THEN Query1.area, else Sheet1.Column3

Column = IF(ISEMPTY(FILTER(Query1, Query1[destination])), FILTER(Query1, Query1[area]), Sheet1[Column3])

The reason why I create the new column in Sheet1 data source is because the FILTER option was only available there. I could refer form one data source to the other only through Sheet1 new column.

The goal: I want to have a column, which gives Area based on Destination.

This is the error I get

Now, regarding the source data columns:

- query1.destination can be null;

- sheet1.column1 has a set of destuinations and is never null;

- sheet1.column3 can be null or empty;

- query1.destination=sheet1.column1.

[g_georgiev444 · Accepted solution]

I found a solution to my problem. 

I created a new MERGED AS NEW table that combines Query1 and Sheet1. Relationship was done in the same way as before: Query1[destination]=Sheet1[column1].

In this way the new additional column easily accepts the formula:

Column = IF(ISEMPTY(MergedTable[destination]), MergedTable[area], MergedTable[Column3])

Then all visuals are done with the columns from the merged table.

[amitchandak]

@g_georgiev444 , You are returning the filter, which gives a table. You need use function like Sum/min/max/sumx/minx/maxx etc to get only one value

[g_georgiev444]

Hello @amitchandak ,

Thank you for the answer. Unfortunately I do not see how it is possible to use min/max when the match happens on 1 specific cell from sheet1.column1. I do not want to return the whole table. There is only 1 record that corresponds to the destination from Query1. 

Even if I put min/max in front of the FILTER I still get error.

I need for every row in Query1 to get data in new column depending on the rule above. 

I am watching your videos, but cannot find a similar case.