How to create pbix report under DirectQuery mode?

[EZV12]

Hello everybody,

I'm in trouble with DirectQuery Mode. 

I need to build a report under this mode, but all funtionalities seem to be useless under this mode. I cannot creat fusions, neither add tables or columns...

How can I deal with this mode to build reports? May you please give me some suggestions?

Many thanks in advance.

Rachel

[aj1973]

Hi @EZV12 

Direct Query mode has its limitations. So you either use Import mode or a mix of DQ and Import to achieve what you need. here is the difference and limitations

[EZV12]

Dear Amine,

Thank you very much for this comparaison table, however, I still have difficulties to put them into practice. Is it possible for you to share some sample files pbix under DQ mode?

Many thanks in advance.

Rachel

[aj1973]

@EZV12 

Sorry but I don't think sharing files in DQ mode would help you with your issue. There are plenty of videos out there that you use to better understand. However if you expose where it's blocking for you in details I can help you better. 

[EZV12]

Hello Amine,

Here is the link https://1drv.ms/u/s!AnbNXrfr-X8jdCh4I-viYzEgvpw?e=kGjuRO for a sample file pbix. I created it by entering the data manually, but the real report is under DQ mode. Maybe you may help me to figure out some DAX formulas, which will be great! 

Here below please some images in which I explained my problems and asked questions:

I appreciate a lot your kind help!!

Best regards

Rachel

[aj1973]

Ok, am gonna answer your issues picture by picture.

Picture 1 :

The answer is no, you can't group 2 tables under DQ mode nor Use DAX( it is considered as a Complex DAX functions, check the table). If you want to use DQ in this case then add a view to your data base where you group these 2 tables then upload it to your Model as DQ mode. DQ utilise un connection directe a la BD, donc le model et les tables sont déjà en place. 

Picture 2 : 

1) I don't see why you are not able to connect the 2 tables!!! I think you can but it is Many to many relationship which is not recommended.

2) I will get back to you on it if it is really neccessary...

Picture 3 : 

Wrong, the tables can be connected, le message est claire: c'est juste la relation est de cadinalité plusieurs à plusieurs qui est pas recommandé dans un model Power Bi. Donc la relation est possible mais aussi vous pouvez utiliser DAX pour avoir la formule qui vous convient dans vos calcules.  

Picture 4 :

I the picture, you have tables that are in DQ mode connections, Import Mode and Live connection mode. Those "strange" links indicate the difference between the connection mode of the tables. Why would you want to replace them by DAX!!!

Je sens que il y a beaucoup de chose qui ne sont pas claire pour vous. le Data Modeling in Power Bi est l'étape la plus critique. You need to know from the get going, who and how the user is going to use your report? Why chosing DQ mode vs Import mode!? benefits and limitations!? Am I going to use Time intelligence? What tables do I need for DQ and for what! if no need then Import mode is the best Option. Why not using Dataflow for example!? Performance?...and many other Questions that need to be answered before jumping into developping a report.

Collecter des informations utiles et comparer le Use of Import mode vs DQ mode can save you a lots of time.

[EZV12]

Hello Amine,

Thank you for your reply that helps me clarify several points. However, as I'm a beginner in PBI, I need some help to figure out some DAX formulas mentioned in my questions.

If you can help, it would be great.

Best regards

Rachel

[aj1973]

Hi Rachel,

Picture 1:

Picture 2:

1)

2) First point : did not understand your question

Second point: Where are those values or columns? "Shipments" and "shipments delay"

Picture 3:

Same as Picture 2, 1). You can, but it is many to many relationship and you just need to be carefull how to use calculations.

Picture 4 :

Already answered this Question in my previous reply.

Just to add, not everything is resolved by DAX, sometimes by adding a value or field to a card or visual can give what you are looking for.

I understand that you are new to PBI but some basic knowledge is neccessary and I can still help you.

[EZV12]

Hello Amine,

Here is a more detailed picture for my requests of DAX formulas:

Again, the link for the updated pbix file : https://1drv.ms/u/s!AnbNXrfr-X8jdeHINqfL4vNG-kY?e=WRfzpA

please help, thanks!

Rachel

[EZV12]

Dear Amine,

Many thanks for your kind reply and help.  

I updated the pbix, here is the link https://1drv.ms/u/s!AnbNXrfr-X8jdeHINqfL4vNG-kY?e=WRfzpA Then here below I explained more about my requests for DAX formulas:

Please help! Thanks a lot!

Rachel