Merge and expand in direct query

[Max19]

Hello Community,

I would like to have your assistance in an error that I encountered in direct query mode. I know this is not supported in direct query mode. Atleast, could someone please help me with an alternate way other than importing the data.

Subject: I have extracted two tables separately from two different databases in a server. Now, I would like to merge Table 1, with Table 2 using a related column named Id from table 1 and Status Id from table 2. I did not using merge queries. But the problem is I got table 2 like nested table after merging. Now that I was trying to expand it but encountered an error saying this query is not supported in direct qquery mode and asking me to switch to import query.

Could someone suggest an alternate way to continue in direct query mode?

[Max19]

Hey @BeaBF / @uzuntasgokberk ,

I tried using the following formula in creating a new column, but end up in an error. 

Previously, I tried creating tables manually using the necessary columns from the table. It then worked but here, it is not working even though there is a relationship established already. VIEW_RFIS is from a database 1 and rest of the tables from different database2

Could you now please help in creating a column in VIEW_RFIS table. That way I can establish realtime data. Else, I will have to manually establish a formula using the data from the necessary column (Statusp[Name]).

Note: please ignore the hidden tables.

[BeaBF]

@Max19 Hi! where are u trying to calculate the new column? 

[Max19]

New column is in View_RFIS table

[BeaBF]

@Max19 if the solution by relationship worked well, accept the answer as solution, to help other users! 

BBF

[Max19]

Thank you so much @BeaBF  @uzuntasgokberk!

I will try and check that and will get back to you!

[uzuntasgokberk]

Hello @Max19 ,

Actually, you can use merge table too. İt is suport query folding some datasource such as MSSQL, SAP BW etc.. But you can use ınner join and left join. İf you'd like to use full outer join that is not support query folding which means also encountered an error saying this query is not supported in direct query.

But other option is making a relationship in the model view. For me I prefer to creating a relationship because it more effective.

Best Regards,
Gökberk Uzuntaş

LinkedIn: https://www.linkedin.com/in/g%C3%B6kberk-uzunta%C5%9F-b43906198/

Medium: https://medium.com/@uzuntasgokberk

İf this post helps, then please consider Accept it as solution and kudos to help the other members find it more quickly.

[BeaBF]

@Max19 Hi! 

Instead of merging the tables, use Power BI’s Manage Relationships feature to create a relationship between Table 1 and Table 2 on the Id and Status Id columns. 

With the relationship established, you can use DAX formulas to reference and display related values from Table 2 in visuals. This approach allows you to keep the tables separate but still retrieve relevant data for your analysis without merging.

as you can see, in the same visual i've used the ID of Table1 and a Field of Table2.

if it's ok, please accept my answer as solution.

BBF

[Max19]

Hi @BeaBF, @uzuntasgokberk 

The reason I am asking this is, later I will have to do some other transformations like creating custom and conditional columns.

Any comments on this?

[BeaBF]

@Max19 after set the relationship as above, you can use the RELATED function or simply recall the field in your measure. 
Like:

BBF

[Max19]

Noted. I will definitely try that. Thank you @BeaBF 😊

[BeaBF]

@Max19 Try it, then if it's ok, please accept my answer as solution to help other users!

BBF

[Max19]

Hey @BeaBF ,

I tried creating a relation between the table VIEW_RFIS( (T1) which is my fact table and Workflow Type Status (T2) where the common columns are NotificationWorkflowStatusId from T1 and Status Id from T2.

for your reference. here is the T2 table

WorkflowTypeId	StatusId	StatusName	WorkflowTypeName
2	10	Accepted	Workflow de notificaciones
2	11	Rejected	Workflow de notificaciones
2	12	Cancelled	Workflow de notificaciones
2	15	Issued - Pending Construction Review	Workflow de notificaciones
2	16	Issued - Pending Quality Review	Workflow de notificaciones
2	17	Accepted Contractor	Workflow de notificaciones
2	21	Accepted Contractor	Workflow de notificaciones
2	22	Accepted Contractor and PID	Workflow de notificaciones
2	24	Suspended	Workflow de notificaciones

Now in T1, I wanted a column named Notification Status Name where if the NotificationWorkflowStatusId from T1 matches the  Status Id, I wanted Status Name.

Else for nulls I wanted to remain as null.

After this, I have a column named NotificationStatus in the T1. Now, I wanted another column named NotificationStatusTemp in T1 where I wanted to apply a condition like below

If Notification Status (T1) = "Issued" then it should take the value from the Notification Status Name (FYi, Previously created column) else it should take the value that is in the Notification Status.

Based on this, I should have a column waiting Execution Result based on the below formula:

=if [NotificationStatus] = "Accepted" and [ExecutionStatus] = "Not started" then "Yes" else "No"

which I will use to create another column as shown in below snip

Let me know if you can understand what I was trying to have.


Other Case:

I tried creating a relation between t1 and t2 using the common columns with *Id but when I execute the following dax it is not working and even it is not popping up the LOOKUPVALUE function.

[BeaBF]

@Max19 Can you paste data of table T1 too?

BBF

[Max19]

Hey @BeaBF ,

That's a lot of data which I can not share due to its confidentiality.
But, here is a news. I was able to create some new columns on the table using DAX and considering the values on the T2 as they are just 9.
But, I got to see these symbols when I select the slicer visual. (Select means just the shape not filtering). May I know what this is.

[BeaBF]

@Max19 

The first icon on the left (a bar chart with a funnel) likely signifies options for filtering or setting filters at the visual level. This symbol usually suggests that you can apply specific filters to limit the data shown in the slicer or connected visuals.

The second icon on the right (a circle with a diagonal line) generally provides an option to clear or reset filters. Clicking this icon would remove any active filters applied through the slicer.

When you select the slicer without applying a filter, these icons may appear to give you quick access to filter options or to clear filters directly from the visual interface. They allow for easy management of filters within your data model or report.

BBF

[Max19]

Hey @BeaBF ,

I was able to create some measures and columns using DAX. But without using the T2 table. As the values are just 9, I manually used them in the DAX in an if clause.

But, I don't know when these discontinuities started appearing between most of the  tables
 Please let me know if you have idea