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.
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
Solved! Go to Solution.
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
Proud to be a Super User!
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.
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
Proud to be a Super User!
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 |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |