Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi everyone.
I have a complicated problem, so I create the following example for a better understanding.
I have the following table:
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]:
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.
Solved! Go to Solution.
I solved the problem.
It is in the next post https://community.powerbi.com/t5/Desktop/Help-Store-output-of-a-measure/m-p/957151/highlight/false#M...
😃
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.
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.
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.
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.
I solved the problem.
It is in the next post https://community.powerbi.com/t5/Desktop/Help-Store-output-of-a-measure/m-p/957151/highlight/false#M...
😃
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 can you clarify for me? What was wrong with the result of the formula? Can you post an example of your desired result?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |