Forum Discussion
Complex formulae with data model
Over the years, I have developed a significant excel data sheet which has gotten slower and slower with time.
I have discovered the idea of excel's data model this week and have learnt how to import the data I need from its source CSV to the data model and I now have a power pivot which looks like what I originally had in excel.
What I would like to do next, is to replicate some of the formulae I had in excel to calculate if something should be counted or not.
I would not want the red one to be counted as this row would have been counted in the previous month.
In excel I was using the following formula-
=IF(COUNTIF(B5:M4,">0"),SUM(Patient!B5:M5*0),Patient!N5)
is there a way of achieving this with the data model or would I have to hardcode this into another sheet? I was trying to avoid doing this to minimise the number of formulae...
Many thanks for your help...
2 Replies
Hello Alexis_Piper,
Your current formula works in the worksheet because it checks cells to the left but will not work in the Data Model or Power Pivot. To count only the first occurrence per row by month, unpivot your months in Power Query and add a calculated column: FirstMonth = CALCULATE(MIN(Table[Month]), ALLEXCEPT(Table, Table[RowID])) CountFlag = IF(Table[Month] = Table[FirstMonth], Table[Value], 0) This counts only the first occurrence. Worksheet formulas still work but will slow performance on large datasets.The formula should read...sorry.
=IF(COUNTIF(B5:M5,">0"),SUM(Patient!B5:M5*0),Patient!N5)
