Forum Discussion

g_georgiev444's avatar
g_georgiev444
Helper I
3 years ago
Solved

How to make DAX based on Query and Excel table

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.
Need Help
  • g_georgiev444's avatar
    g_georgiev444
    3 years ago

    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.

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    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's avatar
      g_georgiev444
      Helper I
      3 years ago

      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.

  • g_georgiev444's avatar
    g_georgiev444
    Helper I
    3 years ago

    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.