cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

DAX recursive Calculation in metric and not in column

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:

datamodel.PNG

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)
    Excel_simulation.PNG
  • 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.

PBI_simulation.PNG


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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Helper I
Helper I

Re: DAX recursive Calculation in metric and not in column

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. 🙂

View solution in original post

2 REPLIES 2
Highlighted
Helper I
Helper I

Re: DAX recursive Calculation in metric and not in column

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.

Highlighted
Helper I
Helper I

Re: DAX recursive Calculation in metric and not in column

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. 🙂

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors