Issue after changing direct query mode to import mode

[phchu1226]

Hi,

I just changed the storage mode from direct query to import, others remain unchanged and the table is wrong after that (should be 100%).

I used the same SQL statement to extract data, and also checked the dataset is the same, the relationship between those table is the same, the measure is the same. However, the table is different. 

I dont know what's wrong.. suppose changing the storage mode (just clicking one button) will not affect anything, isn't it?

Anyone can help me? Thank you

direct query:

import:

[Anonymous]

Hi @phchu1226 ,

I am failed to reproduce your error. Please check  if there is a difference in the generated query between the two connection modes.  Try to use Performance Analyzer to check if the comparison make a difference.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Anonymous]

Hi @phchu1226 ,

I tried to reproduce your error, but it didn't work.

Please ensure. 

Whether the number of rows in the table changes before and after the changing. Are A and B in the same table? If it is , please try to re-write a measure. If it still doesn't work, please provide a screenshot with sensitive information obscured.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[phchu1226]

hi @Anonymous,

I changed the highlighted and masked tables from dq to import and tried to rewrite the measure.

It works but I still can't figure out why the measure was affected by the changing of storage mode.

Relationship:

1) A[CUST_NAME] M:1 CUSTOMER[CUST]

2) A[TYPE] M:1 DATE[DATE]

3) B[CUST_NAME] M:1 CUSTOMER[CUST]

4) B[TYPE] M:1 DATE[DATE]

Old measure: (Result is correct before changing the storage mode but wrong after changing)

RATE =

DIVIDE (

SUM ( 'A'[ORDER_PROCESSED] ),

CALCULATE (SUM('B'[COUNT_PO]), ALLEXCEPT ( 'B',B[CUST_NAME],'B'[TYPE] ) ),

0

) + 0

New measure: (Result is correct after chaning the storage mode)

RATE =

DIVIDE (

SUM ( 'A'[ORDER_PROCESSED] ),

CALCULATE (SUM('B'[COUNT_PO]), ALLEXCEPT ( 'B',CUSTOMER[CUST],'DATE'[DATE] ) ),

0

) + 0

[Anonymous]

Hi @phchu1226 , 

Obviously, you have made changes to the formula.

Old measure:   ALLEXCEPT ( 'B',B[CUST_NAME],'B'[TYPE] ) ), 

New measure:ALLEXCEPT ( 'B',CUSTOMER[CUST],'DATE'[DATE] ) ),

I don't have your data to analyze this formula.  

CALCULATE (SUM('B'[COUNT_PO]), ALLEXCEPT ( 'B',B[CUST_NAME],'B'[TYPE] ) ),

CALCULATE (SUM('B'[COUNT_PO]), ALLEXCEPT ( 'B',CUSTOMER[CUST],'DATE'[DATE] ) ),

The two answers may different. You can create measures to compare the differences. Two different formulas lead to different results.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[phchu1226]

hi @Anonymous ,

But my question is that why the result is different while I am just changing the storage mode?

Dataset, relatioship, measure, field name, nothing changed.

[parry2k]

@phchu1226 it could, DQ has a limit of 1 million rows when it is reading the data and that's why I was asking the question to understand if there is anything else. Read limitations here Use DirectQuery in Power BI Desktop - Power BI | Microsoft Docs

✨ Follow us on LinkedIn and  to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

[phchu1226]

@parry2k ,

Yea, DQ has limitation but DQ is the right one in my case.. the result goes wrong after changing to import..

Also, the measure is counted from the dataset (order_process, count_po) and the dataset is the same.. suppose the measure will be the same.. (simple calculation 1/1 = 100%), now the only difference is the measure..

[parry2k]

@phchu1226 I think my fundamental question is, did you create these measures or not? if yes, what is the expression? There is a reason why I'm asking this? At what granularity you are looking at the data? 

[phchu1226]

@parry2k 

Thanks for reply.

First of all, do you know the answer of this question?

Suppose changing the storage mode (just clicking one button) will not affect any data?

did you create these measures or not? what is the expression?

->  I mentioned above, column a / column b  * 100% = column c (measure)

direct query:

import:

At what granularity you are looking at the data? 

[phchu1226]

@parry2k 

What are these measures? What are we comparing? 

As I mentioned above, I just clicked a button from a drop-down list to change the storage mode..

It is not suppose to change my data(I had refreshed manually to make sure it is the latest dataset)? also the measure? as it is just change the storage mode..

The measure is calculating the % from the first two column -> 2/2 = 100% ,1/1 =100% ,etc, ..

I dont know why the calculation will be wrong after changing the mode, now 1/1 = 25%,

no filter involved in the page.

[parry2k]

@phchu1226 what are these measures? What are we comparing? Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

✨ Follow us on LinkedIn and  to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.