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
Anonymous
Not applicable

Saved results of measures

Hi everyone.

 

I have a complicated problem, so I create the following example for a better understanding.

 

I have the following table:

7.PNG

Where Number and Date are given

IE and FE are calculated columns and are given by the user.

IE = DATE(2019;09;01)

 

FE = DATE(2020;01;01)

And total is calculated column and is:

Total = IF(AND('Table'[Date]<'Table'[FE];'Table'[Date]>'Table'[IE]);DATEDIFF('Table'[Date];'Table'[FE];HOUR);0)

 

Then i create the following measure:

Medida = AVERAGE('Table'[Total])

Now, I add to the model the following table call [Dates]:

8.PNG

 And i want to create to the table [Dates] a new calculated column with the value of "Medida" if IE=FI and FE=FF for each row.

 

Do you have any idea?

 

Thanks.

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable
8 REPLIES 8
v-frfei-msft
Community Support
Community Support

Hi @Anonymous ,

 


 And i want to create to the table [Dates] a new calculated column with the value of "Medida" if IE=FI and FE=FF for each row.

 


As your request, by the formula we should get the excepted result. 

Column = CALCULATE(AVERAGE('Table'[Total]),FILTER('Table','Table'[IE] = 'Date'[Fecha Inicial] && 'Table'[FE] = 'Date'[Fecha Final]))

 

If I did not catch you, please share more details about the logic of  your target column.

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Anonymous
Not applicable

Hey @v-frfei-msft 

 

It does not work, appears the error: Expressions that yield variant data-type cannot be used to define calculated columns.

 

Thanks for the reply by the way.

Anonymous
Not applicable

@v-frfei-msft 

 

I change the formula for this:

 

Columna = CALCULATE(FORMAT([Medida];"General Number");FILTER('Table';AND('Table'[IE]=Dates[FI];'Table'[FE]=Dates[FF])))

 

But the calculated column appears with no results.

 10.PNG

 

What I need is a new calculated column in [Table] where evaluates [Medida] with IE=FI and FE=FF, this means repeat the formula in [Medida] changing the inputs IE and FE depending on which row of [Table] is.

Note that the values on IE and FE at the beginning (First image in the post) are the same date in every row.

Anonymous
Not applicable

Tad17
Solution Sage
Solution Sage

So you need:

 

Medida = IF(AND(Table[IE]=Table[FI],Table[FE]=Table[FF]), "Medida", "")

 

If this helps please kudo.

If this solves your problem please accept it as a solution.

Anonymous
Not applicable

@Tad17 

 

Doesn´t work, doesn´t create the correct answer.

@Anonymous  can you clarify for me? What was wrong with the result of the formula? Can you post an example of your desired result?

Anonymous
Not applicable

@Tad17 

 

It should give me a table like this: (for the firsts rows)

9.PNG

 

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.