cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
EZV12
Helper III
Helper III

How to create pbix report under DirectQuery mode?

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

9 REPLIES 9
aj1973
Community Champion
Community Champion

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

aj1973_0-1621124801364.png

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

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
Community Champion
Community Champion

@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. 

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

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:

EZV12_0-1621167460645.pngEZV12_1-1621167475667.pngEZV12_2-1621167489828.pngEZV12_3-1621167503978.png

I appreciate a lot your kind help!!

Best regards

Rachel

aj1973
Community Champion
Community Champion

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.

  

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

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
Community Champion
Community Champion

Hi Rachel,

 

Picture 1:

aj1973_0-1621257171312.png

Picture 2:

1)

aj1973_1-1621258515711.png

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.

  

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

Hello Amine,

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

EZV12_0-1621261716527.png

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

please help, thanks!

Rachel

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:

EZV12_0-1621260854484.png

Please help! Thanks a lot!

Rachel

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors