Forum Discussion

mvelazquez's avatar
mvelazquez
Frequent Visitor
6 years ago
Solved

Issue with Dax

So the requirement is to set up an alert in Power Bi to see if the past 3 days moving average is outside 1 standard deviation of the 30 days before that which means they want to see the change in values only if there's a major change in last 30 days. 
 
Here are the Dax codes that I created :
Bbls = CALCULATE(SUM(Table(DailyBbls)))
3 Day Sum = CALCULATE([Bbls],DATESINPERIOD(Table[FlowDay],LASTDATE(Table[FlowDay]),-3,DAY))
 
3 Day Moving Average = [3 Day Sum]/CALCULATE(DISTINCTCOUNT(Table[FlowDay]),DATESINPERIOD(Table[FlowDay],LASTDATE(Table[FlowDay]),-3,Day))
 
  30 Day Sum = CALCULATE([Bbls],DATESINPERIOD(Table[FlowDay],LASTDATE(Table[FlowDay]),-30,DAY))  
 
  30 Day Moving Average = [30 Day Sum]/CALCULATE(DISTINCTCOUNT(Table[FlowDay]),DATESINPERIOD(Table[FlowDay],LASTDATE(Table[FlowDay]),-30,Day)) 
 
StdDev = 
Var_table = SUMMARIZE(table,[DailyBbls],"Measure",table[30DayMovingAverage])
RETURN STDEVX.P(_table,[Measure])
 
And this is final alert. I thought i can add this in a card and then put the threshold in power bi service to only filters values with 1 but for some reason I was not able to create a calculated column and could only create a measure but on measure i'm not able to create filters.
 
Alert = If([Bbls] > [30DayMovingAverage] +[StdDev],1,0) 
Need Help
  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    6 years ago

    Hi mvelazquez ,

     

    Sorry for that measures are not supported to be used by slicer. But we can use the following steps to meet your requirement.

     

    1. Use Enter data to create a table that contains 0 and 1.

     

     

    Then use value field as a slicer.

     

    2. Then we can create a measure and add the measure on the visual filter. Configure the measure “is 1”.

     

    Visual Control = if([Alert] in Distinct(SlicerTable[Value]), 1, -1)

     

     

    3. At last we create a slicer using [value], and we can get the result like this,

     

     

    If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

    BTW, pbix as attached.

     

    Best regards,

     

    Community Support Team _ zhenbw

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    Community Support
    6 years ago

    Hi mvelazquez ,

     

    Sorry for that measures are not supported to be used by slicer. But we can use the following steps to meet your requirement.

     

    1. Use Enter data to create a table that contains 0 and 1.

     

     

    Then use value field as a slicer.

     

    2. Then we can create a measure and add the measure on the visual filter. Configure the measure “is 1”.

     

    Visual Control = if([Alert] in Distinct(SlicerTable[Value]), 1, -1)

     

     

    3. At last we create a slicer using [value], and we can get the result like this,

     

     

    If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

    BTW, pbix as attached.

     

    Best regards,

     

    Community Support Team _ zhenbw

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.