Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
MJAGUSIAK
Helper I
Helper I

Dataflow issue

All-

im having issues with my data flow talking across all of my tables.

Im trying to use the date table to sort by month and use the client table to sort by brand. but the brand is only sorting for the totals for the year and not for the individual month. Can someone please tell me how I need to setup the dataflow in order for each of the tables to be able to chat with each other. Below is the a copy of the dataflow as well as a picture of the visualization page. In my head, I imagine that i can use the date table as the center point and then reference the two larger tables of the PDE17 file and the CYBS TR files as fact tables to the date table. . But if I am correct, then all the data should take all the way across the entire flow and a filter in one area will filter the rest of the flow. Correct me if i am wrong on this.

MJAGUSIAK_0-1641234993629.pngMJAGUSIAK_1-1641235020128.png

 

1 ACCEPTED SOLUTION

It is good if you follow e.g. star schema in your model. For example all dimension tables like Date should be in single relationship to your fact table/s (one-to-many)




Did I answer your question? Mark my post as a solution!


https://allure-analytics.com/
https://www.youtube.com/channel/UCndD_QZVNB_JWYLEmP6KrpA
https://www.linkedin.com/company/77757292/

Proud to be a Super User!




View solution in original post

9 REPLIES 9
ALLUREAN
Solution Sage
Solution Sage

Hi, @MJAGUSIAK 

You need to fix your data model first, that might cause a lot of issues. It is not recommended using bi-directional relationship in all tables




Did I answer your question? Mark my post as a solution!


https://allure-analytics.com/
https://www.youtube.com/channel/UCndD_QZVNB_JWYLEmP6KrpA
https://www.linkedin.com/company/77757292/

Proud to be a Super User!




Can you explain what you mean by fix my data model? What could be causing the issue? 
Ive spent 8 weeks now trying to self teach Power BI. Not sure if my starting point is screwed up. 

It is good if you follow e.g. star schema in your model. For example all dimension tables like Date should be in single relationship to your fact table/s (one-to-many)




Did I answer your question? Mark my post as a solution!


https://allure-analytics.com/
https://www.youtube.com/channel/UCndD_QZVNB_JWYLEmP6KrpA
https://www.linkedin.com/company/77757292/

Proud to be a Super User!




Ok i just remodeled it to look like this, using the CYBS TR table and PDE 17 table both as fact tables. is this allowed in a star schema? and it wont let me select anything other than M*M. any ideas on that?

MJAGUSIAK_0-1641241737432.png

 

For each fact table start by describing the "grain", eg what does a single row represent and what describes it.

 

For a sales table the grain might be a product, sold to a customer on a specific date. Eg ProductID, CustomerId and Date describe the row. You then have Product, Customer and Date dimensions.

 

With multiple fact tables you want to share those dimensions (these are called conformed dimensions).

 

If you can describe the rows of your fact tables we can then help suggest the dimension tables you need.



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

You might find this useful:

https://www.sqlbi.com/p/introduction-to-data-modeling-for-power-bi-video-course/



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

Much appreciated for the link. I just signed up. 

my cybs Tr table has one row that represents a single transaction for a customer. The PDE17 file, represents a refund on an order that i need to reference to the CYBS TR table. Each row in both tables represent a single transaction. Both of these files I pull directly from my vendors site with the full months worth of data. 

My CYBS TR data has 316 columns for one table and PDE 17 has 48 columns.

I'm assuming most of those 100s of columns describe the transaction. 
You want to use power query to split them into logical groupings in separate dimension tables.

 

If all the data relates to a transaction I'd be tempted to use power query to merge the data on transaction id and end up with a single fact table with your dimensions coming off it.

 

However I still think something is missing from the description of the data. In your latest model you've got a many to many relationship between the two tables which would suggest that the same transaction appears multiple times in both tables or you're joining on the wrong field.



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

exactly. each row represents a single transaction. 

Ive been using power query to append all of the files together into one large file (i have multiple clients and for each client i have a file for every months history= about 200 files) the fields that I am referencing are unique value fields to connect them. I will double check the data to verify this but there shouldnt be any duplicates when using these fields. 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.