Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Dax formula - Live Data Calculated Column workaround

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 go...
Need Help
v-lili6-msft's avatar
v-lili6-msft
Icon for Community Support rankCommunity Support
7 years ago

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's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        v-lili6-msft
        Icon for Community Support rankCommunity Support
        7 years ago

        hi, Anonymous 

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

         

        Best Regards,

        Lin