True of false after specific event

[Jeffrey_VC]

Hello everyone,

We are using Power Bi to check the working of ower UnderGround Container systems.
This containers are emptied one of more time a week, with a automatic sensor. After the emptying we get a system code (code 301). A disposal in the Underground system can only with a RFID card. This disposal give a systemcode 803 or 804.

For testing if the automatic sensor is working we have a pilot that after the emptying de truck driver also make a disposal.

Now I want to make a report that gives me true/false for an emptying with a disposal within 10 minutes after empyting with one of more specific RFID numbers..

Is there a funtion in Dax that support my request?

Thank you for the support!

Greets, Jeffrey

[Anonymous]

Hi@ Jeffrey_VC,

According to your description You want to create a new column of True/False based on a specific event (CompletionCode=301), after comparing the specific event with the following event (CompletionCode=804). Right?

Here are the steps you can follow：

(1)This is my test data:

(2)Create calculated column:

isTrue =

var _Current_SystemCode='Table2'[Systemcode]

var _Current_LocalTime='Table2'[Local Time]

var _Pre_301_Code=MAXX(FILTER('Table2', [Local Time] <= _Current_LocalTime && 'Table2'[Systemcode]=301) ,[Local Time])

return

 IF( _Current_SystemCode =301,BLANK(), IF(DATEDIFF(_Pre_301_Code,_Current_LocalTime,MINUTE)>10  ,"False","True"))

(3)Then you can achieve your needs:

If this solution is different from your needs, you can provide your specific output and we can better help and understand your problem.

Best Regards

[Jeffrey_VC]

Hello @Anonymous ,
I was trying to change your solution to get the right output, but i can't get the right output.

Hopefully you can help me to the right output.

Greets, Jeffrey

[Jeffrey_VC]

Hi Yinginr,

Thank you for your answer.

It is in the right direction. Only I need a true/false on the "systemcode" 301.

True: within 10 minutes systemcode 803 or 804
False: outside 10 minutes systemcode 803 or 804.

Greets, Jeffrey

[Anonymous]

Hi  @Jeffrey_VC ,

Please update the formula of the calculated column [isTrue] as below and check if it can return the correct result...

isTrue =
VAR _Current_SystemCode = 'Table2'[Systemcode]
VAR _Current_LocalTime = 'Table2'[Local Time]
VAR _Pre_301_Code =
    MAXX (
        FILTER (
            'Table2',
            [Local Time] <= _Current_LocalTime
                && 'Table2'[Systemcode] = 301
        ),
        [Local Time]
    )
RETURN
    IF (
        _Current_SystemCode = 301,
        IF (
            DATEDIFF ( _Pre_301_Code, _Current_LocalTime, MINUTE ) > 10,
            "False",
            "True"
        ),
        BLANK ()
    )

Best Regards

[Jeffrey_VC]

Hello @Anonymous ,

You helped me before is this case.

I have an additional challenge on this.
Based on a certain internal cardnumber I want to be able to indicate whether it is true or false.

I've tried everything but I can't figure it out.

True: within 10 minutes systemcode 803 or 804 and internalnumbers "0000000000000002" and "0000000000000003"
False: outside 10 minutes systemcode 803 or 804 or/and other internalnumers then "0000000000000002" and "0000000000000003"

Example:

Hopefully you can help me (again) 😅

Greets Jeffrey

[Jeffrey_VC]

Hi @Anonymous,

Thank you for your anwer.

I have change the formula:

isTrue =

VAR _Current_SystemCode = 'Query1'[CompletionCode]

VAR _Current_LocalTime = 'Query1'[Local Time]

VAR _Pre_301_Code =

    MAXX (

        FILTER (

            'Query1',

            [Local Time] <= _Current_LocalTime

                && 'Query1'[CompletionCode] = 301

        ),

        [Local Time]

    )

RETURN

    IF (

        _Current_SystemCode = 301,

        IF (

            DATEDIFF ( _Pre_301_Code, _Current_LocalTime, MINUTE ) > 10,

            "False",

            "True"

        ),

        BLANK ()

    )

After the change every systemcode 301 is now true (between and outside the 10 minutes)

Printscreen:

[Anonymous · Accepted solution]

Hi @Jeffrey_VC ,

Please update the formula of your calculated column [isTrue] as bleow and check if it can return the correct result... You can find the details in the attachment.

isTrue = 
VAR _curcode = 'Query1'[CompletionCode]
VAR _curltime = 'Query1'[Local Time]
VAR _8034time =
    CALCULATE (
        MIN ( 'Query1'[Local Time] ),
        FILTER (
            'Query1',
            'Query1'[Name] = EARLIER ( 'Query1'[Name] )
                && 'Query1'[CompletionCode]
                IN { 803, 804 }
                && 'Query1'[Local Time] > _curltime
        )
    )
RETURN
    IF (
        _curcode = 301,
        IF ( DATEDIFF ( _curltime, _8034time, MINUTE ) > 10, "False", "True" ),
        BLANK ()
    )

Best Regards

[Jeffrey_VC]

@AnonymousYes great! That looks good.

Thank you very much.

Greets

[Greg_Deckler]

@Jeffrey_VC I think that this technique could help: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395....
The basic pattern is:
Column = 
  VAR __Current = [Value]
  VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])

  VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
  __Current - __Previous

It seems to me to be a similar kind of problem. Otherwise, Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[Jeffrey_VC]

@Greg_Deckler  thnx for your reply:

1 sample data:

Original time	Local Time	Systemcode	RFID	Installation
5-4-2022 06:43	5-4-2022 08:43	301	BA00000000000000	0001
5-4-2022 06:49	5-4-2022 08:49	804	00804D4BD2C1B004	0001
8-4-2022 08:04	8-4-2022 10:04	301	BA00000000000000	0001
8-4-2022 09:24	8-4-2022 11:24	804	00804D4BD2C1B004	0001

2
First emptying:
In my sample data the first empyting is on 5-4-2022 08:43 en after the emptying 5-4-2022 08:49 there is a disposal (RFID 00804D4BD2C1B004) This output needs to be true (disposal less then 10 minutes after emptying. And card with RFID number that is know as disposal card of truck driver.

Second emptying is on 8-4-2022 10:04 en after the emptying 8-4-2022 11:24 there is a disposal (RFID 00804D4BD2C1B004) This outpus needs to be false (disposal is more then 10 minutes after emptying).

3. Hopefully this is clear

Greets, Jeffrey