DAX recursive Calculation in metric and not in column

[SergioTorrinha]

Hi all,

I would like to have a recursion metric (not a calculated column) to use in a chart.
Alow me to explain the problem (you can find attached a PBI desktop file with dummy data for this problem) :

In my data model I have 3 tables:

• dimDates - is just a simple calendar table, with dates between 01-08-2020 and 30-09-2020
• dimGeo - it's a table representing geografical regions(ID) and subregions(geoID)
• facTable - it's where data is residing

Here is how data model is built:

So, I would like to build 3 principal metrics:

• New cases - this one is easy to compute. It's nothing more then a sum of values filtered by a couple Types:
  New Cases = CALCULATE( [Numb Cases], facTable[Type] in {"type_a","type_b","type_c"})
• Refil cases - this is the tricky one. 🙂
  This metric should be calculated as follows:
  In first week of data, its always 0 in any region/subregion
  In second week, and onwards, its given by the sum of New cases last week and the Refill cases from last week
  I'v made an Excel simulation to help understand (I also included this metric in PBI, althought I know it's wrongly calculated)
  
• Total cases - this metric is "simply" the sum of New Cases + Refil cases

What I have managed to do properly, in the attached powerbi desktop file, was to build the New cases metric. Total and refill  metrics are wrongly calculated and I would like you to help me out on this task, which I understand it might not be simple.

What I would like to build:

• a bar chart like the one i did in excel but with correctly calculated new, refill and total figures.
• 2 line charts: one for new cases only and other one with refill cases.

Let me know if you need any more information.
Thank you

[SergioTorrinha]

Hi all!

I was thinking that, perhaps, a calculated table would help with the refill calculations, specifically for when I want to report that metric with the geoID's.

Would this be a viable solution?
What would be the performance impacts of this, when data grows along the time (this is weekly data, by the way)?

Thanks in advance.

[SergioTorrinha · Accepted solution]

Hi all!

I solved my own problem! 🙂
Here's how I did it, in case someone else is coming to this problem in future:

The solution found was not with DAX, but with good old SQL.
What I did was to generate in SQL a query that returns a table specifically for the Refill cases calculations. This table resulted from a simple CROSS JOIN, something along this lines:

SELECT DISTINCT
a.ProductID
,DATEADD(WW, DATEDIFF(WW, 7, '01-01-' + LEFT( dateID,4 ) ) + ( CAST( RIGHT( dateID, 2 ) AS int)-1), 7) WeekStart
,b.[geoID]
,b.[Type]
,b.value as Refills
FROM facTable a
CROSS JOIN (
SELECT
ProductID
,dateID as dateCode
,[geoID]
,[Type]
,SUM( [value] ) as value
FROM facTable
WHERE
DimProductID = 'xpto'
AND Type in ('a','b','d')
AND dateID< ( SELECT MAX( dateID) FROM facTable WHERE ProductID = 'xpto')
GROUP BY [ProductID], [dateID ], [geoID],[Type]
) as b
WHERE a.ProductID = 'xpto' AND DateCode< a.dateID

Then I loaded this table into PBI and connected to the respectivedimensional tables, and thats it 🙂
Hope this helps someone in future.
Thanks. 🙂