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.
Hello 🙂
My table follows this structure:
Product_Code | Classification | Machine | Time |
AAA | 1 | MACHINE_01 | 26/04/2021 05:18:00 |
AAA | 3 | MACHINE_01 | 26/04/2021 08:19:20 |
AAA | 3 | MACHINE_01 | 26/04/2021 10:28:29 |
BBB | 3 | MACHINE_02 | 26/04/2021 18:00:00 |
BBB | 3 | MACHINE_02 | 26/04/2021 18:00:00 |
CCC | 1 | MACHINE_01 | 26/04/2021 07:00:00 |
CCC | 1 | MACHINE_01 | 26/04/2021 07:05:00 |
CCC | 3 | MACHINE_01 | 26/04/2021 07:11:00 |
CCC | 1 | MACHINE_01 | 26/04/2021 07:19:00 |
DDD | 2 | MACHINE_03 | 26/04/2021 10:07:09 |
DDD | 1 | MACHINE_03 | 26/04/2021 10:58:10 |
EEE | 3 | MACHINE_02 | 26/04/2021 11:10:10 |
EEE | 3 | MACHINE_02 | 26/04/2021 11:29:00 |
EEE | 3 | MACHINE_02 | 26/04/2021 11:37:15 |
I would like to create a measure that serves as a counter for the following occurrence:
If the same classification occurs for the same product and Machine 3 times in a row (based on the Time column) the counter measure must add +1.
So, for my example table above, the counter measure should bring me a value 1, because only the EEE product had 3 equal classifications in a row and for the same product and the same machine.
Can someone help me?
Solved! Go to Solution.
Hi @Anonymous ,
Sorry for the late reply!
First create an index column;
Then create 2 columns as below:
Check =
var _classification= 'Table'[ Classification]
var _code='Table'[Product_Code]
var _machine='Table'[ Machine]
var _count=CALCULATE(COUNTROWS('Table'),FILTER(ALL('Table'),'Table'[ Classification]=_classification&&'Table'[ Machine]=_machine&&'Table'[Product_Code]=_code))
Return
_count
count =
var _nextvalue= CALCULATE(MAX('Table'[Check]),FILTER(ALL('Table'),'Table'[Product_Code]=EARLIER('Table'[Product_Code])&&'Table'[Index]=EARLIER('Table'[Index])+1))
var _previousvalue=CALCULATE(MAX('Table'[Check]),FILTER(ALL('Table'),'Table'[Product_Code]=EARLIER('Table'[Product_Code])&&'Table'[Index]=EARLIER('Table'[Index])-1))
Return
IF('Table'[Check]=3&&_nextvalue=3&&_previousvalue=3,1,BLANK())
And you will see:
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
Hi @Anonymous ,
Why CCC is not included in the result?
CCC | 1 | MACHINE_01 | 26/04/2021 07:00:00 |
CCC | 1 | MACHINE_01 | 26/04/2021 07:05:00 |
CCC | 3 | MACHINE_01 | 26/04/2021 07:11:00 |
CCC | 1 | MACHINE_01 |
26/04/2021 07:19:00 |
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
Hi, @v-kelly-msft
Because I would like the measure to count 3 equal classifications in a row.
CCC has two classifications = 1, then one classification = 3 and then one classification = 1.
So CCC had 3 classifications = 1, but it wasn't 3 in a row
Hi @Anonymous ,
Sorry for the late reply!
First create an index column;
Then create 2 columns as below:
Check =
var _classification= 'Table'[ Classification]
var _code='Table'[Product_Code]
var _machine='Table'[ Machine]
var _count=CALCULATE(COUNTROWS('Table'),FILTER(ALL('Table'),'Table'[ Classification]=_classification&&'Table'[ Machine]=_machine&&'Table'[Product_Code]=_code))
Return
_count
count =
var _nextvalue= CALCULATE(MAX('Table'[Check]),FILTER(ALL('Table'),'Table'[Product_Code]=EARLIER('Table'[Product_Code])&&'Table'[Index]=EARLIER('Table'[Index])+1))
var _previousvalue=CALCULATE(MAX('Table'[Check]),FILTER(ALL('Table'),'Table'[Product_Code]=EARLIER('Table'[Product_Code])&&'Table'[Index]=EARLIER('Table'[Index])-1))
Return
IF('Table'[Check]=3&&_nextvalue=3&&_previousvalue=3,1,BLANK())
And you will see:
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
@Anonymous
Here's a measure that does it. But be careful as it respects all the filters put on the table. That means, for instance, that after filtering you can obtain a classification run that will qualify as per your requirements but if you remove the filter, the same set of previously filtered rows will NOT qualify anymore. Please play with the measure to see how it behaves.
If you don't slice by Time and/or by Classification, the result will be as you'd expect. But if you start slicing by the latter, you have to understand that then you are potentially filtering out rows that might disturb the time sequence of classifications and hence produce a run that will qualify as per the requirements. Just play with the measure and you'll see it works correctly BUT HONORS all filters on the table.
Counts =
var vMinimumRunLength = 3
var vResult =
SUMX(
SUMMARIZE(
T,
T[Product_Code],
T[Machine]
),
// Need to check if there is a classification
// that occurs at least 3 times in a row for
// the combination above and if it does, then
// the expression below should return 1.
var vClassificationWithTime =
CALCULATETABLE(
SUMMARIZE(
T,
T[Classification],
T[Time]
)
)
var vClassificationWithTimeOrder =
ADDCOLUMNS(
vClassificationWithTime,
"@TimeOrder",
var vCurrentTime = T[Time]
return
RANKX(
vClassificationWithTime,
T[Time],
vCurrentTime,
ASC
)
)
var vClassificationWithChangeIndicator =
ADDCOLUMNS(
vClassificationWithTimeOrder,
"@ChangeIndicator",
var vCurrentClassification = T[Classification]
var vCurrentTimeOrder = [@TimeOrder]
var vPreviousClassification =
MAXX(
FILTER(
vClassificationWithTimeOrder,
[@TimeOrder] = vCurrentTimeOrder - 1
),
T[Classification]
)
var vIsChange =
vPreviousClassification <> vCurrentClassification
return
int( vIsChange )
)
var vClassificationWithGroupId =
ADDCOLUMNS(
vClassificationWithChangeIndicator,
"@GroupId",
var vCurrentTimeOrder = [@TimeOrder]
var GroupId =
SUMX(
filter(
vClassificationWithChangeIndicator,
[@TimeOrder] <= vCurrentTimeOrder
),
[@ChangeIndicator]
)
return
GroupId
)
var vSameClassificationLongestRun =
MAXX(
GROUPBY(
vClassificationWithGroupId,
[@GroupId],
"@RunLength",
SUMX( CURRENTGROUP(), 1 )
),
[@RunLength]
)
var vIsLongestRunEnough =
vSameClassificationLongestRun >= vMinimumRunLength
return
INT( vIsLongestRunEnough )
)
return
vResult
Hi, @Anonymous
Thank you very much for your answer 🙂 it took me a while to answer because I was testing your measurement, as you said. I realized that, as you informed, the filter in the tables can change the count number of the measure, which I think can be a problem.
Why? I wanted to create this counting measure just to create an email alert in Power BI based on that measure. My Power BI updates once a day, so I wanted that when I updated, this measure would count the occurrences of the previous day, and if the value was greater than 0, then the email would be sent to people, informing them that there were products with 3 same classifications in a row for the same machine.
I'm still trying to use your measure, but sometimes, when I filter only on a specific day, it shows a different occurrence count than it should show.
I don't know what else I can do 😞
@Anonymous
Hi there. If you want to stop the measure being sensitive to some filters, then create a new one based on the above that will be to your liking. You could do something like:
[Counts Adjusted] =
calculate(
[Counts],
// removing filters, if any, from
// Column1, Column2,... of Table1
all( Table1[Column1], Table1[Column2],... ),
// same for Table2... and so on...
all( Table2[Column1], Table2[Column2],... ),
...
)
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 |
---|---|
112 | |
97 | |
84 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |