Power BI report by creating relationships and formulas

[bzeeblitz]

Goal: To complete the power BI report by esnuring all necessary relationships are created and formulas are applied for automated daily reporting.

Scope:

1.Link data tables(Demand,OpenItem,Cycle)in power Bi (see table below)

2.Add calculations for POstatus,inventory etc

3.Format and visulaize the report to be clear and actionable

Criteria:

1.Ensure relationships between all data tables are correctly created in power BI

2.All necessary formulas for inventory ,POtracking are applied correctly

3.Report is properly formatted and is ready for automated refresh and email distribution

4.Report should reflect data from the previous day and calculate values based on this data correctly

Table	Common column	Relationship	Add calculated column1	Add calculated column 2
Demand	Item Number	No duplicates item number	Column Name is  Hand>9 months Hand>9months=if(‘Demand’[Months of available inventory]>9,”More than 9 months onhand”,”Less than 9 months onhand”)	Column Name is  Hand>6months Hand>6months=if(‘Demand’[Months of available inventory]>6,”More than 6 months onhand”,”Less than 6 months onhand”)
Open Item	Item#	Item number can occur multiple times	Column Name is createdwhen Createdwhen=if(‘openitem’[Date:LineCreated].[Daya]=day(Today()-1),”PO created Yesterday”,”PO not created yesterday)
Cycle	Item#	Item Number can occur multiple times

Outcome: A power BI report automatically refreshes daily and displays accurate metrics

1.createdwhen should have filter applied-createdyesterday (available in openitem)

2.Onhand>9months should have filter applied-more than 9 months(available i demand)

dashboard showing below table and metric

Metric:count of PO# whencreated yesterday and onhand>9months

Columns from cycle tab are order.card type,onhand>9months from demand and rest from openitem tab

Could you please help me to build report as tables are already created but i need help hime creating reports but im unable to drag columns from multiple tables in report list view as per above criteria

[lbendlin]

 im unable to drag columns from multiple tables in report list view 

what list view?  Did you mean a table visual?  Please provide a more detailed explanation of what you are aiming to achieve. What have you tried and where are you stuck?

[bzeeblitz]

I'm trying to achieve by creating dashboard which is showing below table and metric

Metric:count of PO# whencreated yesterday and onhand>9months

Columns from cycle tab are order.card type,onhand>9months from demand and rest from openitem tab

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[bzeeblitz]

I'm trying to create dashboard by dragging below columns from 3 different tables

createdwhen

onhand>9months

ItemNumber

PO#

VendorName

cardType

and i need to check below criteria for expected outcome values

criteria: count of PO# (PO# column is in table cycle) whencreated equals yesterday (when created column is in POItem table) and onhand>9months(this column is in demand table)

Table Name:Demand ;Note:No duplicate Item Number
Item Number	onhand>9 months	onhand>6 months
P123-AB-GG-AA-US	Less Than 9 months onhand	Less Than 6 months onhand
P123-AB-T-Z-US	more Than 9 months onhand	moreThan 6 months onhand
P123-E-BB-Z-US	Less Than 9 months onhand	Less Than 6 months onhand
215B14567	more Than 9 months onhand	more Than 6 months onhand

Table Name :cycle;Note:Multiple times Item# found
Item#	Order:Card Type	PO#
1-UPRIGHT	Card	21390
1-UPRIGHT	card	21390
1-UPRIGHT	card	21390
567890	Non card	21391
567890	non card	21391
567891	card	21392

Table Name : POItem;Note:Multiple times Item# found
Item#	Createdwhen	Vendor name
36 upright	PO not created yesterday	Welldone
36 upright	PO not created yesterday	welldone
36 upright	pO  created yesterday	welldone
38 upright	PO not created yesterday	vendor1

[bzeeblitz]

Im using Table visuals and however i managed to drag the columns by creating relationships for below tables

But im not sure i got to drag and drop the column only after creating below relationships but im not sure getting error this relationship has cardinality many to many.This should only be used if it is expected that neither column item number contains unique values 

Relationship1

From demand table To Open item table cardinality many tomany cross filter direction both

Make this relationship active

Relationship2

From Cycle table To openitem table cardinality many to many cross filter direction both

Make this relationship active