Measure instead of a new table

[Pikachu-Power]

Hi all,

I have a table that looks like following:

Then I created a new table like:

New_Table =
VAR _SUMMARIZE = (FILTER(Table, Table[name1] = "X" && Table[name2] = "Y"),
Table[id_2],
Table[start_date],
"min_id", MIN(Table[id]),
"end_date", MIN(Table[end_date]))

Then I calculated the days and used the average in visuals:

days =
DATEDIFF(new_table[start_date], new_table[end_date], DAY)

Now I was asking myself if it is necessary to create a new table. Is it possible to create a measure for the average of days without creating a new table? I started with a measure but have problems to implement the MIN:

days =
CALCULATE(AVERAGEX(Table, DATEDIFF(Table[start_date], Table[end_date], DAY)),
          FILTER(Table, Table[name1] = "X" && Table[name2] = "Y"))

Thanks for ideas!

Many greetings.

[v-yalanwu-msft · Accepted solution]

Hi， @Pikachu-Power ；

I seem to understand your logic. I modified the following data to prevent accidents, and then only two measures need to be created.

days = 
var _mindid=CALCULATE(MIN('Table'[id]),FILTER(ALL('Table'),[name1]="X"&&[name2]="Y"&&[id_ 2]=MAX('Table'[id_ 2])))
return 
IF(MAX('Table'[id])=_mindid, DATEDIFF(MAX('Table'[start_ date]),MAX('Table'[end_ date]),DAY))

day average = AVERAGEX('Table',[days])

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Pikachu-Power]

Great! Interesting approach. I get the same values as in the new created table. 

[v-yalanwu-msft]

Hi, @Pikachu-Power ;

Modify it.

day average = AVERAGEX(SUMMARIZE('Table','Table'[id_ 2],"1",[minid]),[minid])

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Pikachu-Power]

something is strange 😐 we dont use days. at the end it should be days instead of minid. but then we get the same wrong results before. the right results in .pbix are coincidence.

[v-yalanwu-msft]

Hi， @Pikachu-Power ；

Based on my simple files and data, can you share the results you expect to produce?

[Pikachu-Power]

Only average for the restrictions with X, Y and MIN (as if i have created a new table):

On a card we would like to see 1+4+5 / 3 = 3,33

But maybe to create a new table is indeed the best way to do that? 

[v-yalanwu-msft]

Hi, @Pikachu-Power ;

As minid ,you could try it.

minid = CALCULATE(MIN('Table'[id]),FILTER(ALL('Table'),[name1]="X"&&[name2]="Y"&&[id_ 2]=MAX('Table'[id_ 2])))

and about averge days:

days = DATEDIFF(MAX('Table'[start_ date]),MAX('Table'[end_ date]),DAY)

day average = AVERAGEX(FILTER(ALL('Table'),[name1]="X"&&[name2]="Y"&&[id_ 2]=MAX('Table'[id_ 2])),[days])

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Pikachu-Power]

@amitchandak

Good idea but i dont see the consideration of MIN(Table[id]) in your formula. I added it but unfortunately i get different results as if i create a new table. 

@community support 

The day average dont respect MIN(Table[id]). For example in first row we have in average 1 day and not 2. And at the end i would need the number in a card... it looks like that the calculation works only in a table. 

[amitchandak]

@Pikachu-Power , Try like

New measure =
AverageX( SUMMARIZE(FILTER(Table, Table[name1] = "X" && Table[name2] = "Y"),
Table[id_2],
Table[start_date],
"_end_date", MIN(Table[end_date])) , datediff([Start_date], [_end_date], day))

Or

New measure =
AverageX( SUMMARIZE(FILTER(Table, Table[name1] = "X" && Table[name2] = "Y"),
Table[id_2],
"_start_date", MIN(Table[start_date]),
"_end_date", MIN(Table[end_date])) , datediff([_Start_date], [_end_date], day))