Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi
I am attempting to recreate an Excel document in which the result in time t depends on whether the calculation in t is greater than in t-1 but I'm not having much luck. If the calculation in t > calculation in t-1 then it should use t and if not should carry forward t-1 (the result illustrated below).
26,930 | 26,930 | 54,983 | 54,983 | 54,983 | 55,592 | 57,097 |
For context, the figures above are are calculated numbers of people who have been infected, and thus should not go lower.
I would simply import the results of the calculation into Power BI from Excel, but I need a couple of the parameters of the calculation to be dynamic, which I have achieved through using drop down filters and completing the calculation via a measure.
I hope this, or some other work around, is possible. Any help towards figuring this out would be greatly appreciated! Please let me know if any clarification is required.
Solved! Go to Solution.
Hi @Doxa ,
Just modify the calculated column to a measure,see below:
previous one =
VAR a=CALCULATE(MAX('Table'[Result Currently Produced]),FILTER('Table','Table'[Index]=MAX('Table'[Index])-1))
Return
IF(MAX('Table'[Result Currently Produced])>a,MAX('Table'[Result Currently Produced]),a)
After that,combine the measure of parameter to make it dynamic.
Hi @Doxa ,
The content is not enough to answer your query. Can you pleasea add more details like smaple data, etc.?
Thanks,
Pragati
Of course. Here is a sample of the data from which the calculation is made for those who have been infected over time:
Country/Region | Date | Population | Deceased | W.A. Doubling Time | Infected |
Italy | 12/03/2020 | 60,431,283 | 827 | 1.9 | 1,985,253 |
Italy | 13/03/2020 | 60,431,283 | 1266 | 2.1 | 2,777,841 |
Italy | 14/03/2020 | 60,431,283 | 1441 | 2.2 | 2,994,083 |
Italy | 15/03/2020 | 60,431,283 | 1809 | 2.2 | 3,757,544 |
Italy | 16/03/2020 | 60,431,283 | 2158 | 2.5 | 4,099,267 |
Italy | 17/03/2020 | 60,431,283 | 2503 | 2.6 | 4,511,024 |
Italy | 18/03/2020 | 60,431,283 | 2978 | 2.8 | 4,985,303 |
Italy | 19/03/2020 | 60,431,283 | 3405 | 3.0 | 5,355,590 |
Italy | 20/03/2020 | 60,431,283 | 4032 | 3.1 | 6,010,133 |
Italy | 21/03/2020 | 60,431,283 | 4825 | 3.3 | 6,889,756 |
Italy | 22/03/2020 | 60,431,283 | 5476 | 3.4 | 7,607,952 |
Italy | 23/03/2020 | 60,431,283 | 6077 | 3.5 | 8,119,577 |
Italy | 24/03/2020 | 60,431,283 | 6820 | 3.7 | 8,533,561 |
Italy | 25/03/2020 | 60,431,283 | 7503 | 4.0 | 8,842,008 |
Italy | 26/03/2020 | 60,431,283 | 8215 | 4.2 | 9,161,069 |
Italy | 27/03/2020 | 60,431,283 | 9134 | 4.4 | 9,588,656 |
Italy | 28/03/2020 | 60,431,283 | 10023 | 4.7 | 10,042,461 |
Italy | 29/03/2020 | 60,431,283 | 10779 | 4.8 | 10,385,752 |
Italy | 30/03/2020 | 60,431,283 | 11591 | 5.1 | 10,624,358 |
Italy | 31/03/2020 | 60,431,283 | 12428 | 5.4 | 10,787,259 |
Italy | 01/04/2020 | 60,431,283 | 13155 | 5.6 | 10,890,371 |
Italy | 02/04/2020 | 60,431,283 | 13915 | 5.9 | 10,947,247 |
Italy | 03/04/2020 | 60,431,283 | 14681 | 6.3 | 10,952,014 |
Italy | 04/04/2020 | 60,431,283 | 15362 | 6.6 | 10,952,014 |
Italy | 05/04/2020 | 60,431,283 | 15887 | 6.8 | 10,952,014 |
Italy | 06/04/2020 | 60,431,283 | 16523 | 7.2 | 10,952,014 |
Italy | 07/04/2020 | 60,431,283 | 17127 | 7.7 | 10,952,014 |
Italy | 08/04/2020 | 60,431,283 | 17669 | 8.0 | 10,952,014 |
Italy | 09/04/2020 | 60,431,283 | 18279 | 8.4 | 10,952,014 |
Italy | 10/04/2020 | 60,431,283 | 18849 | 8.8 | 10,952,014 |
Italy | 11/04/2020 | 60,431,283 | 19468 | 9.2 | 10,952,014 |
Italy | 12/04/2020 | 60,431,283 | 19899 | 9.5 | 10,952,014 |
Italy | 13/04/2020 | 60,431,283 | 20465 | 9.9 | 10,952,014 |
Italy | 14/04/2020 | 60,431,283 | 21067 | 10.4 | 10,952,014 |
Italy | 15/04/2020 | 60,431,283 | 21645 | 10.8 | 10,952,014 |
Italy | 16/04/2020 | 60,431,283 | 22170 | 11.1 | 10,952,014 |
Italy | 17/04/2020 | 60,431,283 | 22745 | 11.5 | 10,952,014 |
Italy | 18/04/2020 | 60,431,283 | 23227 | 11.9 | 10,952,014 |
Italy | 19/04/2020 | 60,431,283 | 23660 | 12.3 | 10,952,014 |
The calculation also requires two other parameters:
- Time to Death: Set to 14, although variable in Power BI
- IFR: Set to 0.6%, also variable in Power BI
The Excel formula is as follows (replacing cell references with names):
Infected = IF(IFERROR((2 * "Time to Death"/"W.A. Doubling Time") * ("Deceased" / "IFR"),) > Infected Calculation Previously, IFERROR((2 * "Time to Death"/"W.A. Doubling Time") * ("Deceased" / "IFR"),), Infected Calculation Previously)
E.g. if this formula was in F2, it would refer to F1 if F2 did not produce a larger value.
Hi @Doxa,
I made a sample table to show you how to get the value of "t-1",see below:
1.First go to query editor>add column >Index column;
2,Then create a measure as below:
Measure =
CALCULATE(MAX('Table'[Value]),FILTER(ALL('Table'),'Table'[Index]=MAX('Table'[Index])-1))
Or a calculated column as below:
Column = CALCULATE(MAX('Table'[Value]),FILTER('Table','Table'[Index]=EARLIER('Table'[Index])-1))
Finally you will see:
For the related sample .pbix file ,pls click here.
Thank you very much for your response.
I tried something similar myself to try and fix the issue, but by creating index columns and merging the data with itself in the query editor so i had data for time t and t-1 alongside each other. I then created two measures: one to complete a calculation using time t data, and another that used t-1 data:
Current Infected = ((2 * MAX('Time to Death Selection'[Time to Death])/MAX(CountryRegion[W.A. Doubling Time])) * (MAX(CountryRegion[Modified Deceased])/MAX('IFR Selection'[IFR])))
Prev Infected = ((2 * MAX('Time to Death Selection'[Time to Death])/MAX(CountryRegion[W.A. Doubling Time Prev])) * (MAX(CountryRegion[Modified Deceased Prev])/MAX('IFR Selection'[IFR])))
In the measures above, "Time to Death" and "IFR Selection" are the same values in both equations.
The result is the following table:
I then created a measure to compare the current and previous calculation to find the largest, but this doesn't compare the current infected to the largest infected number calculated so far, which has resulted in the infected number falling, rather than staying the same unless the next calculation produces a higher result.
For example, say the results below are the outcomes of a measure:
Result Currently Produced | Desired Result |
1 | 1 |
3 | 3 |
4 | 4 |
2 | 4 |
3 | 4 |
6 | 6 |
7 | 7 |
6 | 7 |
I hope this provides some clarity and thanks again for the assistance - very much appreciated!
Hi @Doxa ,
First get the value of t-1 row,using below dax expression;
Column =
VAR a=CALCULATE(MAX('Table'[Result Currently Produced]),FILTER('Table','Table'[Index]=EARLIER('Table'[Index])-1))
Return
IF('Table'[Result Currently Produced]>a,'Table'[Result Currently Produced],a)
Then create a measure as below:
Measure =
var a=CALCULATE(MAX('Table'[Result Currently Produced]),FILTER(ALL('Table'),'Table'[Index]=SELECTEDVALUE('Table'[Index])-1))
var b=CALCULATE(MAX('Table'[Column]),FILTER(ALL('Table'),'Table'[Index]=SELECTEDVALUE('Table'[Index])-1))
Return
IF(MAX('Table'[Result Currently Produced])>a&&MAX('Table'[Result Currently Produced])>b,MAX('Table'[Result Currently Produced]),b)
Finally you will see:
For the related .pbix file ,pls click here.
Thanks very much for the help - looking really good!
I'm not sure that working from a column will work in the context of what I am trying to achieve as the formula has parameters that are adjustable by filters. Your final result for the measure is what I am after, in that it doesn't change unless the result is bigger, however, it is my understanding that columns are not dynamic and thus the calculation cannot be adjusted for changes to "Time to Death" and "IFR" as per my measure equations to calculate the number infected:
Infected = ((2 * MAX('Time to Death Selection'[Time to Death])/MAX(CountryRegion[W.A. Doubling Time])) * (MAX(CountryRegion[Modified Deceased])/MAX('IFR Selection'[IFR])))
I am aiming for the equation to recalculate if, for example "IFR" is changed from, say 0.6% to 0.4% or "Time to Death" changed from 14 to 12.
I hope that makes sense - really appreciate the assistance.
Hi @Doxa ,
Just modify the calculated column to a measure,see below:
previous one =
VAR a=CALCULATE(MAX('Table'[Result Currently Produced]),FILTER('Table','Table'[Index]=MAX('Table'[Index])-1))
Return
IF(MAX('Table'[Result Currently Produced])>a,MAX('Table'[Result Currently Produced]),a)
After that,combine the measure of parameter to make it dynamic.
Hi Kelly
Apologies for the delayed response. Thanks for your help so far, much appreciated.
"Result Currently Produced" is actually the result of a dynamic measure rather than a column, as described in a previous post (formulas below) so will this work for that situation?
Infected = ((2 * MAX('Time to Death Selection'[Time to Death])/MAX(CountryRegion[W.A. Doubling Time])) * (MAX(CountryRegion[Modified Deceased])/MAX('IFR Selection'[IFR])))
In the above formula, Time to Death, W.A. Doubling Time, Modified Deceased and IFR are columns imported from Excel, with IFR and Time to Death variable via filter.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |