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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

circular reference

2017-08-11_16-48-48.jpg

 

Hi All,

 

Hoping someone can assist in overcoming the dreaded circular reference error in DAX measures.

 

I'm re-building an excel file in Power BI. The circular ref excel file is a "very" cut down version of the original file. The columns in blue are data (only in the excel file - I have them as DAX in my Power BI file) and the others are measures.

 

In essence, I want to track what can be delivered based on what has:

 

  1. Actually delivered
  2. The Ability to Deliver and;
  3. Upcoming demand.

With these 3 inputs, I have to calculate:

 

  1. Carryover (Daily): Difference of available demand and ability to deliver on the day
  2. Usable ability to deliver: Min of available demand and ability to deliver
  3. Available demand: Sum of actual demand plus carryover from previous day. This is the same as Demand for the 1st day of the month. The demand for the 1st day of the month includes the carryover from the previous month.
  4. Actual Carryover: Difference of demand and what was actually delivered
  5. Actual Available Demand: Sum of demand and actual carry over demand from the previous day. This is the same as Demand for the 1st day of the month.
  6. Actual usable ability to deliver: Blank for past days in the month. For days today plus 14, the minimum of Ability to Deliver Actual Available Demand OR the Ability to Deliver for any day(s) post today plus 14.

 

I have entered the excel formulae above the header row. Helping overcome circular reference would be a great quick measure to add to the quick measure toolkit available within Power BI.

 

Your help is greatly appreciated.

2 REPLIES 2
v-caliao-msft
Employee
Employee

@Anonymous,

 

In your sample data, are there only 3 columns, AbilityToDeliver, ActualDeliver and Demand?

 

From your requirement, it should be have AbilityToDemand and ActualDemand. Please provide us more detail sample data, so that we can make further analysis.

 

Regards,

Charlie Liao

Anonymous
Not applicable

Hi @v-caliao-msft,

 

Thanks for your response. I've updated the file with additional columns. This is the link

 

Demand is Orders (customer orders)

 

The spreadsheet has 2 worksheets. Version 1 is the original and Version 2 contains the additional and updated columns.

 

I've updated the column headers to be more descriptive.

 

Appreciate your assistance.

 

Regards,

Neville

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.