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.
Hi, Please help!!
Left side in the below image is the data set. I need to count the number of consecutive occurences of digit 1.
Count has to happen based on the selected date in the report. Like if select 01-May-2020, then consec count = 3
Solved! Go to Solution.
Hi, @Anonymous
Based on your description, I created data to reproduce your scenraio. The pbix file is attached in the end.
Table:
You may create a measure as below.
Result =
var tab =
ADDCOLUMNS(
ALL('Table'),
"L1",
CALCULATE(
MAX('Table'[Gen. Date]),
FILTER(
ALL('Table'),
'Table'[Loc. No.]=EARLIER('Table'[Loc. No.])&&
'Table'[Gen. Date]<EARLIER('Table'[Gen. Date])&&
'Table'[S+U=24]=1
)
),
"L",
CALCULATE(
MAX('Table'[Gen. Date]),
FILTER(
ALL('Table'),
'Table'[Loc. No.]=EARLIER('Table'[Loc. No.])&&
'Table'[Gen. Date]<EARLIER('Table'[Gen. Date])
)
)
)
var newtab =
ADDCOLUMNS(
tab,
"flag",
IF(
[L1]=[L]&&NOT(ISBLANK([L])),
1,0
)
)
var t =
ADDCOLUMNS(
newtab,
"Re",
var _loc = [Loc. No.]
var _date = [Gen. Date]
return
IF(
[S+U=24]=1,
IF(
ISBLANK([L1])||[flag]=0,
1,
1+
COUNTROWS(
FILTER(
newtab,
[Loc. No.]=_loc&&
[Gen. Date]<=_date&&
[flag]=1&&
NOT(ISBLANK([L]))
)
)
)
)
)
var _result =
SUMX(
SUMMARIZE(
'Table',
'Table'[Loc. No.],
"x",
SUMX(
FILTER(
t,
[Gen. Date]=SELECTEDVALUE('Table'[Gen. Date])&&
[Loc. No.]=EARLIER('Table'[Loc. No.])
),
[Re]
)
),
[x]
)
return
IF(
ISBLANK(_result),
0,
_result
)
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Anonymous
Based on your description, I created data to reproduce your scenraio. The pbix file is attached in the end.
Table:
You may create a measure as below.
Result =
var tab =
ADDCOLUMNS(
ALL('Table'),
"L1",
CALCULATE(
MAX('Table'[Gen. Date]),
FILTER(
ALL('Table'),
'Table'[Loc. No.]=EARLIER('Table'[Loc. No.])&&
'Table'[Gen. Date]<EARLIER('Table'[Gen. Date])&&
'Table'[S+U=24]=1
)
),
"L",
CALCULATE(
MAX('Table'[Gen. Date]),
FILTER(
ALL('Table'),
'Table'[Loc. No.]=EARLIER('Table'[Loc. No.])&&
'Table'[Gen. Date]<EARLIER('Table'[Gen. Date])
)
)
)
var newtab =
ADDCOLUMNS(
tab,
"flag",
IF(
[L1]=[L]&&NOT(ISBLANK([L])),
1,0
)
)
var t =
ADDCOLUMNS(
newtab,
"Re",
var _loc = [Loc. No.]
var _date = [Gen. Date]
return
IF(
[S+U=24]=1,
IF(
ISBLANK([L1])||[flag]=0,
1,
1+
COUNTROWS(
FILTER(
newtab,
[Loc. No.]=_loc&&
[Gen. Date]<=_date&&
[flag]=1&&
NOT(ISBLANK([L]))
)
)
)
)
)
var _result =
SUMX(
SUMMARIZE(
'Table',
'Table'[Loc. No.],
"x",
SUMX(
FILTER(
t,
[Gen. Date]=SELECTEDVALUE('Table'[Gen. Date])&&
[Loc. No.]=EARLIER('Table'[Loc. No.])
),
[Re]
)
),
[x]
)
return
IF(
ISBLANK(_result),
0,
_result
)
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous - OK, if this has to be dynamic then it has to be a measure, not a column. So, perhaps something like this:
Measure =
VAR __Table =
ADDCOLUMNS(
ADDCOLUMNS(
'Table',
"Next",MINX(FILTER(ALL('Table'),[Gen Date]>EARLIER([Gen Date])),[Gen Date])
),
"Diff",([Next] - [Gen Date])*1.
)
RETURN
COUNTROWS(FILTER(__Table,[Diff]=1))
Wasn't able to test. If you provide data in text in a table that I can copy, will mock it up in a PBIX for you.
@Greg_Deckler - Thank you helping me on this. Please find below the sample data. a pbix file will help.
Gen. Date | Loc. No. | GF | FM | S | U | S+U=24 |
01-Apr-20 | 123 | 0.00 | 5.80 | 0.00 | 0.50 | 0 |
02-Apr-20 | 123 | 0.00 | 12.80 | 0.00 | 1.00 | 0 |
29-Apr-20 | 123 | 3.70 | 0.00 | 12.00 | 12.00 | 1 |
30-Apr-20 | 123 | 4.70 | 0.00 | 12.00 | 12.00 | 1 |
01-May-20 | 123 | 0.00 | 0.00 | 12.00 | 12.00 | 1 |
02-May-20 | 123 | 0.00 | 0.00 | 0.00 | 0.00 | 0 |
07-May-20 | 123 | 0.00 | 0.00 | 0.00 | 0.00 | 0 |
08-May-20 | 123 | 0.00 | 0.00 | 11.00 | 13.00 | 1 |
30-May-20 | 123 | 0.00 | 0.00 | 0.00 | 0.00 | 0 |
31-May-20 | 123 | 0.80 | 0.00 | 0.00 | 0.00 | 0 |
01-Jun-20 | 123 | 0.00 | 0.00 | 0.00 | 0.00 | 0 |
02-Jun-20 | 123 | 0.00 | 0.00 | 0.00 | 0.00 | 0 |
03-Jun-20 | 123 | 0.00 | 0.00 | 0.00 | 0.00 | 0 |
04-Jun-20 | 123 | 0.00 | 0.00 | 0.00 | 0.00 | 0 |
08-Jun-20 | 123 | 0.00 | 0.00 | 0.00 | 0.00 | 0 |
12-Jun-20 | 123 | 0.00 | 0.00 | 0.00 | 0.00 | 0 |
30-Jun-20 | 123 | 3.60 | 0.00 | 8.00 | 16.00 | 1 |
01-Apr-20 | 212 | 4.40 | 0.00 | 0.00 | 0.00 | 0 |
02-Apr-20 | 212 | 1.40 | 0.00 | 0.00 | 0.00 | 0 |
29-Apr-20 | 212 | 0.20 | 0.00 | 0.00 | 0.00 | 0 |
30-Apr-20 | 212 | 0.00 | 0.00 | 0.00 | 0.00 | 0 |
01-May-20 | 212 | 1.50 | 1.00 | 0.00 | 0.60 | 0 |
02-May-20 | 212 | 0.00 | 0.00 | 0.00 | 0.00 | 0 |
07-May-20 | 212 | 1.00 | 0.00 | 0.00 | 0.00 | 0 |
08-May-20 | 212 | 0.20 | 0.10 | 0.00 | 0.00 | 0 |
30-May-20 | 212 | 12.00 | 12.00 | 0.00 | 0.00 | 0 |
31-May-20 | 212 | 12.00 | 12.00 | 0.00 | 0.40 | 0 |
01-Jun-20 | 212 | 8.00 | 16.00 | 0.00 | 0.00 | 0 |
02-Jun-20 | 212 | 5.00 | 19.00 | 0.00 | 0.00 | 0 |
03-Jun-20 | 212 | 0.00 | 0.00 | 0.00 | 0.00 | 0 |
04-Jun-20 | 212 | 0.00 | 0.00 | 0.00 | 0.00 | 0 |
05-Jun-20 | 212 | 12.00 | 12.00 | 0.00 | 0.00 | 0 |
08-Jun-20 | 212 | 12.00 | 12.00 | 0.00 | 0.00 | 0 |
12-Jun-20 | 212 | 0.00 | 0.00 | 0.00 | 0.00 | 0 |
30-Jun-20 | 212 | 11.00 | 13.00 | 10.00 | 14.00 | 1 |
I applied both suggestions from Amit and Greg and below is the result.
@Anonymous , Create a new column like this and add that
if(not(isblank(maxx(filter(Table,[Gen Date] = earlier([Gen Date])-1),[Gen Date]))),1,0)
Thank you @amitchandak . I created a column with your suggested defintion and included in the below post. Request you to review and let me know. Should I change to get the correct result?
@Anonymous - I have included a PBIX file below sig. You want Table (36). Not sure if it is what you want. Are you trying to emulate S+U=24 column is that the desired output? Please confirm or deny. If no, then what is the desired output?
Column =
VAR __NextDate = MINX(FILTER(ALL('Table (36)'),[Gen. Date]>EARLIER([Gen. Date]) && [Loc. No.]=EARLIER([Loc. No.]) && [S+U=24]=1),[Gen. Date])
VAR __Next = MAXX(FILTER('Table (36)',[Gen. Date]=__NextDate),[S+U=24])
RETURN
IF([S+U=24]=1 && __Next=1,1,0)
@Greg_Deckler - Below is the output I am trying to get..For a date selected I want to see the consecutive count that represents the outage. Its like if Scheduled (S) + Unscheduled (U) = 24 hrs in a day, then it means there is a outage on the machine/location.
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 |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |