The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
I have a created a measure table to to enable metric selection in visuals.
The table Measure_Select looks like this
MEASURE
Measure 1
Measure 2
I am using this table to create a Measure with a Switch function.
Select a Measure = SWITCH(FIRSTNONBLANK(Measure_Select[Select Measure], Measure_Select[Select Measure]), "Measure 1", SUM(Table[MetricName_Measure_1]),"Measure 2", SUM(Table[MetricName_Measure_2]))
The table and the measurement allow me now to create a slicer where a user can switch between these 2 metrics in a visual.
So far, so good.
In addition to those I have create a table where I define Time Periods for dates (e.g. "ThisMonth", "LastMonthToDate", etc.).
This is what I want to solve now. How can I create a Measure now that would bascially do the following:
Divide "Selected Measure for visual (e.g. Measure 1)" with Filter Value "ThisMonth" of Column "Period" by "Selected Measure for visual (e.g. Measure 1)" with Filter Value "LastMonthToDate" of Column "Period".
As far as I know, I cannot use CALCULATE or SUM because those don't support Created Measures.
Obviously, I want to use this formular to calculate the change in metric between two periods.
The challenge is that the measure changes based on the user input and there needs to be a variable. The measure table has 20 different measure names.
Solved! Go to Solution.
I am not quite sure why it is working now but somehow it does:
Calc MoM YoY = Calculate(Measure_Select[Select A Measure],Period[Period]="ThisMonth")/Calculate(Measure_Select[Select A Measure],Period[Period]="MoM LastYearToDate")-1
For some reason Calculate accepts the calculated measure now...
Hi @toulou,
>>Obviously, I want to use this formular to calculate the change in metric between two periods.
The challenge is that the measure changes based on the user input and there needs to be a variable. The measure table has 20 different measure names.
You can try to add a measure to merge these measures, then use switch or if function to result the specify value.
Result=
var conditionResult=//check the condition to choose a result measure.
return
SWITCH(conditionResult,1,[Measure1],2,"[Measure2]",3,[Measure3],...)
>>As far as I know, I cannot use CALCULATE or SUM because those don't support Created Measures.
No, you can use functions on measure, for example:
SUMX(Table,[Measure])
Calculate(MAXX(Filter(Table,condition),[Measure]),Filter(table,condition))
Regards,
Xiaoxin Sheng
I am not quite sure why it is working now but somehow it does:
Calc MoM YoY = Calculate(Measure_Select[Select A Measure],Period[Period]="ThisMonth")/Calculate(Measure_Select[Select A Measure],Period[Period]="MoM LastYearToDate")-1
For some reason Calculate accepts the calculated measure now...
Hi @toulou what about using a Period Table, which will make it easier to create the custom periods that you are after?
And then you can use your periods to easily create the calculations you require also?
I am certain that you can the simply use the Period Names to Dynamically Filter?
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
101 | |
97 | |
80 | |
62 | |
57 |
User | Count |
---|---|
264 | |
120 | |
115 | |
83 | |
70 |