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...
So far i can see and know you’re definitely on the right track, moving this into the Data Model — that’s exactly the kind of scenario where Power Pivot really starts to pay off in both performance and maintainability.
What your Excel formula is doing is essentially checking:
“Has this row already had a value in any previous month? If yes, don’t count it again.”
In the Data Model, rather than replicating that exact formula pattern, the more natural (and efficient) approach in DAX is to reframe the logic slightly:
Identify the first month where a value exists, and only count that occurrence.
Assuming your data is structured with one row per record per month (i.e., a proper “long” format), you could approach it like this:
1. Calculate the first month with a value per row/entity
FirstMonth :=
CALCULATE (MIN ( 'Table'[Month] ),
FILTER (ALLEXCEPT ( 'Table', 'Table'[ID] ),
'Table'[Value] > 0))2. Then create a measure to count only that first occurrence
CountOnce :=
IF (MAX ( 'Table'[Value] > 0 ) &&
MAX ( 'Table'[Month] ) = [FirstMonth],1,0)This way, the “red 1” you highlighted naturally drops out of the calculation, because it’s not the first occurrence for that row.
A couple of additional thoughts that might help:
- If your current structure still has months as columns (Jan, Feb, Mar…), it’s worth unpivoting that in Power Query — DAX works much more cleanly and efficiently with a normalized model.
- Measures (rather than calculated columns) will usually give you better flexibility if you’re reporting in PivotTables or Power BI.
Overall, you’re absolutely heading in the right direction — moving this logic into the model instead of worksheet formulas is exactly how you keep things scalable as your dataset grows.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and like it!
This will help all forum participants.
7 Replies
So far i can see and know you’re definitely on the right track, moving this into the Data Model — that’s exactly the kind of scenario where Power Pivot really starts to pay off in both performance and maintainability.
What your Excel formula is doing is essentially checking:
“Has this row already had a value in any previous month? If yes, don’t count it again.”
In the Data Model, rather than replicating that exact formula pattern, the more natural (and efficient) approach in DAX is to reframe the logic slightly:
Identify the first month where a value exists, and only count that occurrence.
Assuming your data is structured with one row per record per month (i.e., a proper “long” format), you could approach it like this:
1. Calculate the first month with a value per row/entity
FirstMonth :=
CALCULATE (MIN ( 'Table'[Month] ),
FILTER (ALLEXCEPT ( 'Table', 'Table'[ID] ),
'Table'[Value] > 0))2. Then create a measure to count only that first occurrence
CountOnce :=
IF (MAX ( 'Table'[Value] > 0 ) &&
MAX ( 'Table'[Month] ) = [FirstMonth],1,0)This way, the “red 1” you highlighted naturally drops out of the calculation, because it’s not the first occurrence for that row.
A couple of additional thoughts that might help:
- If your current structure still has months as columns (Jan, Feb, Mar…), it’s worth unpivoting that in Power Query — DAX works much more cleanly and efficiently with a normalized model.
- Measures (rather than calculated columns) will usually give you better flexibility if you’re reporting in PivotTables or Power BI.
Overall, you’re absolutely heading in the right direction — moving this logic into the model instead of worksheet formulas is exactly how you keep things scalable as your dataset grows.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and like it!
This will help all forum participants.
Thank you so much- with your help along with Olufemi7 I have sorted it!! So happy!!
I also can't get the formula to work- am I putting this in as a measure or somewhere in the power query editor?
As far as I can see, I don't think I need to unpivot the data- because the image I have shared above, is a pivot table that has come from the data where every contact is listed as a separate row.
OK, I thikn I have found a work around because I actually have a column which contains the first date, however this first date appears for every record with that client ID. I can count the number of times this date appears, but this is obviously the number of records for this client id.
Is it possible to count not just the first instance in the month that this appears, but, for example count it in Jan but then count how many contacts were in Jan.
I have now have two tables, one counting all contacts, which is fine, i need that too, then another counting the first date. I would like to be able to, on the first dates table, somehow say that ok, if blank, then do nothing, but if there is a date, look in that month on the all contracts table and give me that number.
Not sure if it's possible? Would like to avoid going back to the formulae because as you say, it gets so slow!
Thank you so much for your reply. I appreciate your help.
I should have been more precise in my description. The reason for the B5:M5 count was because I want to count eg Jan 25 IF it has not been counted in the last 12 months. If that row has already been counted in the last 12 months, I want to return 0 hence the SUM(Patient!B5:M5*0.
That's to say, although in the example, M5 was the first instance so I wanted to count this one, it might not always be the FIRST, there might have been one 18 months before. Is that possible to achieve or is the first instance the only way?
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)
