Dax formula - Live Data Calculated Column workaround

[Anonymous]

I'm trying to create a new measure in PowerBI to correctly capture current inventory totals.  I'm dealing with a live connection so I am unable to create a calculated column for this solution.  My goal is to correctly capture the total inventory for all plants.

Background:

We have 39 plants around the US that are supposed to enter in inventory at the end of each day.  The problem is that some days the plants are too busy and forget so nothing is entered, so the inventory total for that plant is blank. 

Data:

The problem with my solution is that because the total inventory for day 6 is not blank, the inventory 56 measure will pull that total, instead of summing the new column.

ParentServiceProviderName	Daily A Pallet Total 56	Daily A Pallet Total 6	Daily A Pallet Total 5
Denver	50	50	40
Boston	30	30	25
Atlanta	20		20
Current Total	80	80	85
Correct Total	100	80	85

[v-lili6-msft]

hi, @Anonymous 

This looks like a measure totals problem. Very common. See this post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

For your case, you could try formula as below:

newMeasure56 = var _table=SUMMARIZE(FactCycleCount,FactCycleCount[ParentServiceProviderName],"total",[Measure56]) return
SUMX(_table,[total])

Result:

Best Regards,

Lin

[Anonymous]

Thank you for the reply.  I'm currently digging through this information and am confident I'll be able to figure out a solution.  

The solution provided has a semantic error at line 1, position 5, reported by Analysis Services: 'The syntax for '_Table' is incorrect'

newMeasure56 = var _Table = SUMMARIZE(FactCycleCount,DimServiceProvider[ParentServiceProviderName],"total",[Daily A Pallet Total 56]) return
SUMX(_table,[total])

[Anonymous]

Edit:  Now struggling to find a solution.  I can't figure out why the above Dax expression won't work.

After searching through the forums I tried the solution below with no avail.

	Mymeasure2 = IF(HASONEFILTER(DimServiceProvider[ParentServiceProviderName]),
		IF(FactCycleCount[Daily A Pallet Total 6] = BLANK(), 
		FactCycleCount[Daily A Pallet Total 5], FactCycleCount[Daily A Pallet Total 6]),
		SUMX(FILTER(FactCycleCount, VALUES(DimServiceProvider[ParentServiceProviderName])), [Daily A Pallet Total 56])
		)

[v-lili6-msft]

hi, @Anonymous 

Please share some sample data with the data model and expected output. 

Best Regards,

Lin

[Anonymous]

Thank you for the reply.  The best I can do without sharing sensitive material is below.  

There tables I'm working with are below:

• The DimServiceProvider contains a field called ParentServiceProviderName, which contains a list of all of our plants.  There's 39 of them.  Theres a Second field called ServiceProviderName, which contains sub-locations for our plants.  I need to get the inventory for each ParentServiceProviderName.
• The DimProduct contains fields that are  used to identify the different types of pallets that we use. The only one of interest is "A Grade" because I can simply copy the "A Grade" inventory total to the other types of pallets.
• The DimDate contains all of the date fields.  The one of interest is DimDate[FiscalDayofWeek], which contains the numbers 1 through 7, corresponding from Sunday through Saturday.  At the end of each fiscal day, besides Sunday (1) and Saturday (7), plants will enter their inventory.  In order to calculate the inventory totals for the prior week, we generally set a Visual filter on FiscalDayofWeek = 6.  Which is the inventory entered on Friday.
• The FactCycleCount contains several fields of interest.  The main one, Daily A Pallet Total, is how we currently pull inventory.  We then set a visual filter, FiscalDayofWeek = 6.  

  • MEASURE FactCycleCount[Daily A Pallet Total] = SUMX(FILTER(FactCycleCount, 
    	RELATED(DimProduct[NationalProductRollup]) = "A Grade" && FactCycleCount[CycleCountType] = "Daily"), 
    	FactCycleCount[CountTotal])

The problem with this way of doing it is mentioned in the original post above.  Plants will forget to enter their inventory, causing the total to be off.  I need a way to individually capture the most recent entered inventory, or generally just 1-2 days prior.  Below are my measures that I created in an attempt at a solution.

-- Will grab the A Inventory on Day 5	
Daily A Pallet Total 5 = CALCULATE(
	SUM(FactCycleCount[CountTotal]),
	FILTER(
	DimProduct,
	DimProduct[NationalProductRollup] = "A Grade"
	),
	FILTER(
	DimDate,
	DimDate[FiscalDayofWeek] = 5
	)
	)
	
-- Will grab the A inventory on Day 6	
	Daily A Pallet Total 6 = CALCULATE(
	SUM(FactCycleCount[CountTotal]),
	FILTER(
	DimProduct,
	DimProduct[NationalProductRollup] = "A Grade"
	),
	FILTER(
	DimDate,
	DimDate[FiscalDayofWeek] = 6
	)
	)
	
-- Will grab the A inventory on the 6th day, If Friday is blank, grab Thursdays.  
-- This works on an invdividual plant level but not the plants total level
	Daily A Pallet Total 56 = IF(FactCycleCount[Daily A Pallet Total 6] = BLANK(), 
	FactCycleCount[Daily A Pallet Total 5], FactCycleCount[Daily A Pallet Total 6])

Now, after reading the above posts I came up with the following measures, none of which work but I mentioned the errors I get in the comments.

--Receive the following error message: The report measure has a syntax or semantic error at line 3, position 6, reported by Analysis Services: 'The syntax for 'Table56' is incorrect.

	newMeasure56 = 
	var 
		Table56 = SUMMARIZE(FactCycleCount,DimServiceProvider[ParentServiceProviderName],"total",[Daily A Pallet Total 56]) 
	return
	SUMX(Table56,[total])
	

--The report measure mymeasure2 has a syntax or semantic error at line 4, position 31, reported by Analysis Sertices:
--Calculation error in measure: Cannot convert value of 'Atlanta, GA' to type text True/False'
	Mymeasure2 = IF(HASONEFILTER(DimServiceProvider[ParentServiceProviderName]),
		IF(FactCycleCount[Daily A Pallet Total 6] = BLANK(), 
		FactCycleCount[Daily A Pallet Total 5], FactCycleCount[Daily A Pallet Total 6]),
		SUMX(FILTER(FactCycleCount, VALUES(DimServiceProvider[ParentServiceProviderName])), [Daily A Pallet Total])
		)