Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |