Forum Discussion

Dunner2020's avatar
Dunner2020
Post Prodigy
5 years ago
Solved

sumup measure does not return value

I am creating a measure that sums up the count of events failures if the count of event failure passes a certain threshold value (let say 3) and failure event occurred on the same location, same cause, and same equipment involved and happened within last N- months (let say 12). Below is my measure:

Card_measure =
Var _cause = MAX( ‘Sheet1’[Cause] )
Var _loc = MAX( ‘Sheet1’[CircuitID] )
Var _equip = MAX( ‘Sheet1’[Equipment] )
Var _zone = MAX(‘Sheet1’[ZoneSubstation])
Var _mindate = EDATE(EOMONTH(TODAY(),0),-[N Value Value])
var _count = CALCULATE(DISTINCTCOUNT(‘Sheet1’[EventID]),FILTER(‘Sheet1’,‘Sheet1’[CircuitID] ==_loc && ‘Sheet1’[Cause] == _cause &&‘Sheet1’[ZoneSubstation] == _zone && ‘Sheet1’[Actual Interruption Start Time (NZST)]>=_mindate))
Return
Sumx(addcolumns(Summarize( ‘Sheet1’, [Cause],[CircuitID],[Equipment],[ZoneSubstation]), “_1”, IF(_count>[Trigger Value Value],_count) ), [_1])

However, the above measure returned blank when I used it in the card visual. I tried to debug the code and found that the _count variable returns null. I am not sure where I am making the mistake. Any help would be really appreciated.

 

Sample file here

Need Help
Show and Tell
Tips and Tricks
  • DataInsights's avatar
    DataInsights
    5 years ago

    Dunner2020,

     

    Try this measure. Since the card doesn't contain filter context for the four columns (Cause, CircuitID, Equipment, ZoneSubstation), MAX doesn't work as it would in a matrix (for example) where the four columns are displayed. Thus, you have to iterate the table and set the four variables within the ADDCOLUMNS iteration.

     

    Card_measure = 
VAR _mindate =
    EDATE ( EOMONTH ( TODAY (), 0 ), - [N Value Value] )
VAR _trigger = 'Trigger Value'[Trigger Value Value]
VAR _BaseTable =
    ADDCOLUMNS (
        SUMMARIZE (
            Sheet1,
            Sheet1[Cause],
            Sheet1[CircuitID],
            Sheet1[Equipment],
            Sheet1[ZoneSubstation]
        ),
        "@Count",
            VAR _cause = Sheet1[Cause]
            VAR _loc = Sheet1[CircuitID]
            VAR _equip = Sheet1[Equipment] 
            VAR _zone = Sheet1[ZoneSubstation]
            RETURN
                CALCULATE (
                    DISTINCTCOUNT ( Sheet1[EventID] ),
                    FILTER (
                        Sheet1,
                        Sheet1[CircuitID] == _loc
                            && Sheet1[Cause] == _cause
                            && Sheet1[ZoneSubstation] == _zone
                            && Sheet1[Actual Interruption Start Time (NZST)] >= _mindate
                    )
                )
    )
VAR _CalcTable =
    FILTER ( _BaseTable, [@Count] > _trigger )
VAR _result =
    SUMX ( _CalcTable, [@Count] )
RETURN
    _result

     

     

2 Replies

  • DataInsights's avatar
    DataInsights
    Super User
    5 years ago

    Dunner2020,

     

    Try this measure. Since the card doesn't contain filter context for the four columns (Cause, CircuitID, Equipment, ZoneSubstation), MAX doesn't work as it would in a matrix (for example) where the four columns are displayed. Thus, you have to iterate the table and set the four variables within the ADDCOLUMNS iteration.

     

    Card_measure = 
VAR _mindate =
    EDATE ( EOMONTH ( TODAY (), 0 ), - [N Value Value] )
VAR _trigger = 'Trigger Value'[Trigger Value Value]
VAR _BaseTable =
    ADDCOLUMNS (
        SUMMARIZE (
            Sheet1,
            Sheet1[Cause],
            Sheet1[CircuitID],
            Sheet1[Equipment],
            Sheet1[ZoneSubstation]
        ),
        "@Count",
            VAR _cause = Sheet1[Cause]
            VAR _loc = Sheet1[CircuitID]
            VAR _equip = Sheet1[Equipment] 
            VAR _zone = Sheet1[ZoneSubstation]
            RETURN
                CALCULATE (
                    DISTINCTCOUNT ( Sheet1[EventID] ),
                    FILTER (
                        Sheet1,
                        Sheet1[CircuitID] == _loc
                            && Sheet1[Cause] == _cause
                            && Sheet1[ZoneSubstation] == _zone
                            && Sheet1[Actual Interruption Start Time (NZST)] >= _mindate
                    )
                )
    )
VAR _CalcTable =
    FILTER ( _BaseTable, [@Count] > _trigger )
VAR _result =
    SUMX ( _CalcTable, [@Count] )
RETURN
    _result

     

     

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Community Support
    5 years ago

    Hi, Dunner2020 ;

    According to your pbix, You must create two measure not one measure,such as:

    count =
CALCULATE (
    DISTINCTCOUNT ( [EventID] ),
    FILTER (
        ALL ( 'Sheet1' ),
        [Cause] = MAX ( [Cause] )
            && [CircuitID] = MAX ( [CircuitID] )
            && [Equipment] = MAX ( [Equipment] )
            && [ZoneSubstation] = MAX ( Sheet1[ZoneSubstation] )
            && [Actual Interruption Start Time (NZST)]
                <= MAX ( [Actual Interruption Start Time (NZST)] )
            && [Actual Interruption Start Time (NZST)]
                > EOMONTH ( MAX ( [Actual Interruption Start Time (NZST)] ), -12 )
    )
)

    then create a sum measure:

    Card_measure2 = 
var tab=SUMMARIZE('Sheet1',[CircuitID],[Equipment],[Cause],[ZoneSubstation],"_1",IF([count]>[Trigger Value Value],[count]))
return SUMX(tab,[_1])

    The final output is shown below:

    Best Regards,
    Community Support Team_ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.