Forum Discussion
Star schema: table visualization fails
Hello, I am quite new in PBI. I was trying to create a star schema from wide table. I have a fact table:
fact_Bookings =
VAR dataframe =
ADDCOLUMNS(
nsp_contract_report,
"PK_mapped_line",
//map values from dime table with parent table
LOOKUPVALUE(
'bridge_Line'[PK],
'bridge_Line'[Composite_key], 'nsp_contract_report'[Concat_line_table]
)
)
RETURN
SELECTCOLUMNS(
dataframe,
"FK_line", [PK_mapped_line],
// "FK_revision", [PK_mapped_revision],
// "FK_customer", [PK_mapped_customer],
// "FK_contract", [PK_mapped_contract],
"Booking_amount_usd", [Booking_Amount_USD]
)Also I have dim_line table:
dim_Line =
//Collect distinct options about line table
VAR dataframe =
DISTINCT(
SELECTCOLUMNS(
nsp_contract_report,
"Contract_status", nsp_contract_report[Contract_Status],
"Service_product_name", nsp_contract_report[Service_Product_Name],
"Line_date_duration_days", nsp_contract_report[Line_Date_Duration_Days],
"Price_override_reason", nsp_contract_report[Price_Override_Reason],
"NSP_category", nsp_contract_report[NSP_Category],
"SKU_duration_days", nsp_contract_report[SKU_Duration_Days],
"Opportunity", nsp_contract_report[Opportunity],
"Line", nsp_contract_report[Line]
)
)
//Create composite key, the same as in parent table
VAR dataframe_copy =
ADDCOLUMNS(
dataframe,
"Composite_key", [Contract_Status] & "-" & [Service_Product_Name] & "-" & [Line_Date_Duration_Days] & "-" & [Price_Override_Reason] & "-" & [NSP_Category] & "-" & [SKU_Duration_Days] & "-" & [Opportunity] & "-" & [Line]
)
RETURN
SELECTCOLUMNS(
dataframe_copy,
[Contract_Status],
[Service_Product_Name],
[Line_Date_Duration_Days],
[Price_Override_Reason],
[NSP_Category],
[SKU_Duration_Days],
[Opportunity],
[Composite_key],
[Line]
)I have also calculated column PK in dim_Line:
PK =
RANKX(
ALL(
'bridge_Line'
),
'bridge_Line'[Composite_key],,
ASC,
Dense
) -1This are relathionships between tables:
When I try to create a table vizualization for fact_Booking and dim_Line (bridge_line) I have this error:
I was looking for issue for 4 hours. The most interesting thing that I use the same approach in another dashboard and it works. How can I solve the issue, please?
Hi alks_skla_f ,
Thank you for reaching out on the Microsoft Fabric Community Forum.
Also, thanks to lbendlin for the prompt and helpful response.
Here are a few key checks and resolutions to help troubleshoot the issue
Mismatch in Data Types
Check: Ensure FK_line in fact_Bookings and PK in bridge_Line are of the same data type.Fix: If not, use INT() or VALUE() to convert during column creation or in Power Query.
PK Not Unique in dim_Line
Check: PK must be unique in bridge_Line to act as a primary key.Fix: Add this DAX measure in bridge_Line to check for duplicates:
PK_Duplicates =
CALCULATE(COUNTROWS('bridge_Line'), ALLEXCEPT('bridge_Line', 'bridge_Line'[PK]))Empty or Mismatched FK Values
Check: Confirm that FK_line in fact_Bookings actually contains values that exist in bridge_Line[PK].Fix: Use this DAX:
Missing_Lines =
EXCEPT(
VALUES('fact_Bookings'[FK_line]),
VALUES('bridge_Line'[PK])
)If you find our response helpful, we kindly request you to mark it as the accepted solution and provide kudos. This will assist other community members facing similar queries.
Thank you.
5 Replies
Your relationship is between dim and fact. "bridge_line" is likely not wired into that.
Consider doing all the normalization work in Power Query. Avoid bridge tables, they are not required in a well designed data model.
Hi alks_skla_f ,
Thank you for reaching out on the Microsoft Fabric Community Forum.
Also, thanks to lbendlin for the prompt and helpful response.
Here are a few key checks and resolutions to help troubleshoot the issue
Mismatch in Data Types
Check: Ensure FK_line in fact_Bookings and PK in bridge_Line are of the same data type.Fix: If not, use INT() or VALUE() to convert during column creation or in Power Query.
PK Not Unique in dim_Line
Check: PK must be unique in bridge_Line to act as a primary key.Fix: Add this DAX measure in bridge_Line to check for duplicates:
PK_Duplicates =
CALCULATE(COUNTROWS('bridge_Line'), ALLEXCEPT('bridge_Line', 'bridge_Line'[PK]))Empty or Mismatched FK Values
Check: Confirm that FK_line in fact_Bookings actually contains values that exist in bridge_Line[PK].Fix: Use this DAX:
Missing_Lines =
EXCEPT(
VALUES('fact_Bookings'[FK_line]),
VALUES('bridge_Line'[PK])
)If you find our response helpful, we kindly request you to mark it as the accepted solution and provide kudos. This will assist other community members facing similar queries.
Thank you.Hi alks_skla_f,
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.
Regards,
Prasanna Kumar
Hi alks_skla_f ,
Just a gentle reminder — has your issue been resolved? If so, we’d be grateful if you could mark the solution that worked as Accepted Solution, or feel free to share your own if you found a different fix.
This not only closes the loop on your query but also helps others in the community solve similar issues faster.
Thank you for your time and feedback!
Best,
Prasanna Kumar
Hi alks_skla_f,
Just a gentle reminder — has your issue been resolved? If so, we’d be grateful if you could mark the solution that worked as Accepted Solution, or feel free to share your own if you found a different fix.
This not only closes the loop on your query but also helps others in the community solve similar issues faster.
Thank you for your time and feedback!
Best,
Prasanna Kumar
