Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
vslaser
Helper I
Helper I

Re-calculate measure for identified dates

Hi All,

 

I have a schema in which multiple tables are connected and I have a table of measures on these tables. The key table in this schema is a table of Work Orders (WOs) which has a list of dates against which work orders are listed. 

 

vslaser_0-1600156868108.png

 

 

I have now generated a table of start and end dates. I want to re-calculate a measure for these dates as max and min by filtering the WOs table.

 

vslaser_1-1600156892870.png

 

I tried this using the below formula but I did not get any result (Just blank):

calculate(
            calculate([Overall System Availability_product]),
            FILTER(WOs,(SELECTEDVALUE(WOs[REPORTDATE+Time])>=SELECTEDVALUE('Table'[Start])) && 
           (SELECTEDVALUE(WOs[REPORTDATE+Time])<=SELECTEDVALUE('Table'[End])))), 
          ALLSELECTED('Test Freq + Config'[SECE Group]), ALLSELECTED(Measures_Table))
 
I tried this with the filter removed and I got the overall result without taking the start and end dates into consideration. However, I want to filter the WOs data for the start and end dates and ignore the slicer on the page. Any help would be deeply appreciated.
 
Thanks,
Vijay
1 ACCEPTED SOLUTION

I found the perfect solution for this issue. Here are 2 measures I wrote to arrive at the answer:

measure 1:

Var MinDate = values('Table'[Start])
Var MaxDate = values('Table'[End])
Var No_apm_failfix = CALCULATE(calculate(COUNT(WOs[APM_FAILFIX]),(WOs[APM_FAILFIX]= "APMFAIL" || WOs[APM_FAILFIX]= "APMFAILFIX" )),ALLSELECTED('Test Freq + Config'[SECE Group]),WOs[REPORTDATE+Time]<=MaxDate, WOs[REPORTDATE+Time]>=MinDate)
Var No_CM = CALCULATE(CALCULATE(COUNT(WOs[WORKTYPE]), (WOs[CM_HASRAF] == "CMY")), WOs[REPORTDATE+Time]<=MaxDate, WOs[REPORTDATE+Time]>=MinDate)
Var calc_dt = CALCULATE(sum(WOs[DOWNTIME]), (WOs[CM_HASRAF] == "CMY"), WOs[REPORTDATE+Time]<=MaxDate, WOs[REPORTDATE+Time]>=MinDate)
Var date_diff = DATEDIFF(MinDate, MaxDate, DAY)
Var pop = calculate(SUM('Test Freq + Config'[Sum of Pop Counts]), REMOVEFILTERS(WOs[REPORTDATE+Time]))
Var hidden_fail = IFERROR(CALCULATE(No_apm_failfix/(date_diff*pop)),1)
Var hidden_av = iferror(if(hidden_fail>0, (1-(exp(-(hidden_fail*sum('Test Freq + Config'[Test Freq])))))/(hidden_fail*sum('Test Freq + Config'[Test Freq])),1),1)
Var CM_unav = if(calc_dt == 0,0,if(pop == 0, 0,calculate(calc_dt/(date_diff*pop),WOs[REPORTDATE+Time]>=MinDate, WOs[REPORTDATE+Time]<=MaxDate)))
Var element_av = IFERROR(calculate(hidden_av - CM_unav),1)
// Var comb_av = calculate(
// if((VALUES('Test Freq + Config'[Configuration]) == "1oo1"), element_av,if((values('Test Freq + Config'[Configuration]) == "2oo2"), power(element_av,2), if((values('Test Freq + Config'[Configuration]) == "N/A"), 1,if((values('Test Freq + Config'[Configuration]) == "1oo2"),(1 - power(1-element_av,2)),if((values('Test Freq + Config'[Configuration]) == "3oo4"), (4*power(element_av,3) - 3*power(element_av,4)),0))))), ALLSELECTED('Test Freq + Config'[SECE Group]),WOs[REPORTDATE+Time]>=MinDate, WOs[REPORTDATE+Time]<=MaxDate)
Var overall =


CALCULATE(PRODUCTX(
KEEPFILTERS(values('Test Freq + Config'[RBD Group])),
CALCULATE(calculate(
if((VALUES('Test Freq + Config'[Configuration]) == "1oo1"), element_av,
if((values('Test Freq + Config'[Configuration]) == "2oo2"), power(element_av,2),
if((values('Test Freq + Config'[Configuration]) == "N/A"), 1,
if((values('Test Freq + Config'[Configuration]) == "1oo2"),(1 - power((1-element_av),2)),
if((values('Test Freq + Config'[Configuration]) == "3oo4"), (4*power(element_av,3) - 3*power(element_av,4)),1))))),
WOs[REPORTDATE+Time]>=MinDate, WOs[REPORTDATE+Time]<=MaxDate))
))

return
element_av
final measure:
CALCULATE(PRODUCTX(
KEEPFILTERS(values('Test Freq + Config'[RBD Group])),
CALCULATE(calculate(
if((VALUES('Test Freq + Config'[Configuration]) == "1oo1"), [Calculated],
if((values('Test Freq + Config'[Configuration]) == "2oo2"), power([Calculated],2),
if((values('Test Freq + Config'[Configuration]) == "N/A"), 1,
if((values('Test Freq + Config'[Configuration]) == "1oo2"),(1 - power((1-[Calculated]),2)),
if((values('Test Freq + Config'[Configuration]) == "3oo4"), (4*power([Calculated],3) - 3*power([Calculated],4)),1))))))
)))

View solution in original post

12 REPLIES 12
amitjzaveri
Resolver II
Resolver II

Are you looking for a query something like this?

 

CALCULATE(
CALCULATE([Overall System Availability_product],DATESBETWEEN(WOs[REPORTDATE+Time], MAX('Table'[Start]),MAX('Table'[End])),ALLSELECTED('Table'))
,ALL('Test Freq + Config'[SECE Group]), ALL(Measures_Table))

I think this will give a re-calculation for min and max for the 2 columns. I was hoping to get a value for each row. I hope this makes sense. Thanks. Apologies

amitchandak
Super User
Super User

@vslaser , Not very clear .

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

@Amit, can you please send me a contact method? I am unable to upload the pbix file here. What I want to do is to generate a trend with the value re-calculated for each date range in the rows of this table. At the moment, it is not taking into consideration the start and end dates in the table which is why the value remains unchanged in the rows

Hi @amitchandak ,

Please find the file at the below link:

https://drive.google.com/file/d/17ma0SUX4Kx9X9AGwSyXq2VLX39C_H7Ev/view?usp=sharing

Any help would be deeply appreciated.

Thanks,

Vijay

Hi,

Please share the exact result that you are expecting.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@vslaser  Use this formula and I guess it works as per your requirement

 

Calculated = calculate([Overall System Availability_product],DATESBETWEEN(Measures_Table[Date], MAX('Table'[Start]),MAX('Table'[End])),ALLSELECTED(WOs),ALL('Test Freq + Config'[SECE Group]))

 

Not sure why you have used "ALLSELECTED(WOs)" but I kept it as it is. And as mentioned earlier DATESBETWEEN will help you to filter rows by given dates

bi.png

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

 

Cheers!

 


@vslaser wrote:

Hi @amitchandak ,

 

Please find the file at the below link:

 

https://drive.google.com/file/d/17ma0SUX4Kx9X9AGwSyXq2VLX39C_H7Ev/view?usp=sharing

 

Any help would be deeply appreciated.

 

Thanks,

Vijay


 

@amitjzaveri , the solution you provided is only working for 1 of the SECEs and not for others and the values dont seem to be for all the ones in table columns 😞

Hi all,

I used the below formula and am getting a value for each of the rows in the 'Table' table. These values however are still wrong.

calculate( [Overall System Availability_product],
FILTER(ALLSELECTED(WOs),
(
((WOs[REPORTDATE+Time]>=SELECTEDVALUE('Table'[Start]))) &&
(WOs[REPORTDATE+Time]<=SELECTEDVALUE('Table'[End]))
)),
ALLSELECTED('Test Freq + Config'[SECE Group])
)
Regards,
Vijay

I found the perfect solution for this issue. Here are 2 measures I wrote to arrive at the answer:

measure 1:

Var MinDate = values('Table'[Start])
Var MaxDate = values('Table'[End])
Var No_apm_failfix = CALCULATE(calculate(COUNT(WOs[APM_FAILFIX]),(WOs[APM_FAILFIX]= "APMFAIL" || WOs[APM_FAILFIX]= "APMFAILFIX" )),ALLSELECTED('Test Freq + Config'[SECE Group]),WOs[REPORTDATE+Time]<=MaxDate, WOs[REPORTDATE+Time]>=MinDate)
Var No_CM = CALCULATE(CALCULATE(COUNT(WOs[WORKTYPE]), (WOs[CM_HASRAF] == "CMY")), WOs[REPORTDATE+Time]<=MaxDate, WOs[REPORTDATE+Time]>=MinDate)
Var calc_dt = CALCULATE(sum(WOs[DOWNTIME]), (WOs[CM_HASRAF] == "CMY"), WOs[REPORTDATE+Time]<=MaxDate, WOs[REPORTDATE+Time]>=MinDate)
Var date_diff = DATEDIFF(MinDate, MaxDate, DAY)
Var pop = calculate(SUM('Test Freq + Config'[Sum of Pop Counts]), REMOVEFILTERS(WOs[REPORTDATE+Time]))
Var hidden_fail = IFERROR(CALCULATE(No_apm_failfix/(date_diff*pop)),1)
Var hidden_av = iferror(if(hidden_fail>0, (1-(exp(-(hidden_fail*sum('Test Freq + Config'[Test Freq])))))/(hidden_fail*sum('Test Freq + Config'[Test Freq])),1),1)
Var CM_unav = if(calc_dt == 0,0,if(pop == 0, 0,calculate(calc_dt/(date_diff*pop),WOs[REPORTDATE+Time]>=MinDate, WOs[REPORTDATE+Time]<=MaxDate)))
Var element_av = IFERROR(calculate(hidden_av - CM_unav),1)
// Var comb_av = calculate(
// if((VALUES('Test Freq + Config'[Configuration]) == "1oo1"), element_av,if((values('Test Freq + Config'[Configuration]) == "2oo2"), power(element_av,2), if((values('Test Freq + Config'[Configuration]) == "N/A"), 1,if((values('Test Freq + Config'[Configuration]) == "1oo2"),(1 - power(1-element_av,2)),if((values('Test Freq + Config'[Configuration]) == "3oo4"), (4*power(element_av,3) - 3*power(element_av,4)),0))))), ALLSELECTED('Test Freq + Config'[SECE Group]),WOs[REPORTDATE+Time]>=MinDate, WOs[REPORTDATE+Time]<=MaxDate)
Var overall =


CALCULATE(PRODUCTX(
KEEPFILTERS(values('Test Freq + Config'[RBD Group])),
CALCULATE(calculate(
if((VALUES('Test Freq + Config'[Configuration]) == "1oo1"), element_av,
if((values('Test Freq + Config'[Configuration]) == "2oo2"), power(element_av,2),
if((values('Test Freq + Config'[Configuration]) == "N/A"), 1,
if((values('Test Freq + Config'[Configuration]) == "1oo2"),(1 - power((1-element_av),2)),
if((values('Test Freq + Config'[Configuration]) == "3oo4"), (4*power(element_av,3) - 3*power(element_av,4)),1))))),
WOs[REPORTDATE+Time]>=MinDate, WOs[REPORTDATE+Time]<=MaxDate))
))

return
element_av
final measure:
CALCULATE(PRODUCTX(
KEEPFILTERS(values('Test Freq + Config'[RBD Group])),
CALCULATE(calculate(
if((VALUES('Test Freq + Config'[Configuration]) == "1oo1"), [Calculated],
if((values('Test Freq + Config'[Configuration]) == "2oo2"), power([Calculated],2),
if((values('Test Freq + Config'[Configuration]) == "N/A"), 1,
if((values('Test Freq + Config'[Configuration]) == "1oo2"),(1 - power((1-[Calculated]),2)),
if((values('Test Freq + Config'[Configuration]) == "3oo4"), (4*power([Calculated],3) - 3*power([Calculated],4)),1))))))
)))

Thanks @amitchandak ,

I tried the solution you gave me but it looks like it now does not change with the selection of SECE group in the page. The same result is displayed for all SECE groups 😞

@Ashish_Mathur : The result I expected was:

recalculated values of the verall availability for the given dates for the selected SECE group. Unfortunately, I am unable to calculate them here because even with the help @amitchandak provided, the result is not specific to the SECE group. 😞

Regards,

Vijay

Thanks Amit. I will share a sample

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.