Count of tickets within breakdown (data in two not connected tables)

[SanKing]

Dear Power BI Community,

I would like to ask for your help - I have two tables:

Table 1 - with list of tickets & Table 2 - with breakdown time (without aby relation):

Is it possible to count how many tickets were within Breakdown time (from start to end) or mark these tickets?
I tried:

Breakdown tickets? = IF(AND('Table 1'[Ticket Delivery Date]>=MIN('Table 2'[Start]), 'Table 1'[Ticket Delivery Date]<=Max('Table 2'[End])),1,0)
But unfortunately the result it's in 98% 1, exept 3 last days.

Many thanks in advance for any suggestions!

[FreemanZ · Accepted solution]

hi @SanKing 

try to add a column in Table2 with this:

DurationCount = 
VAR _start = [Start]
VAR _end =[End]
RETURN
COUNTROWS(
    FILTER(
        Table1,
        Table1[Date]>=_start
            &&Table1[Date]<=_end
    )
)

i tried and it worked like this:

the Table1 sample:

[SanKing · Accepted solution]

Hi @FreemanZ ,
I would like to mark (in new column) in Table 1 if ticket was affected, means 
IF ticket was printed in breakdown time "YES" (or 1), IF not "NO" (or 0).
Formula:
Breakdown tickets? = IF(AND('Table 1'[Ticket Delivery Date]>=MIN('Table 2'[Start]), 'Table 1'[Ticket Delivery Date]<=Max('Table 2'[End])),1,0)
Doesn't work.
Thank you in advance!

[v-cgao-msft · Accepted solution]

Hi @SanKing ,

If I understand correctly, please try this:

Breakdown tickets? = 
VAR _date = 'Table1'[Ticket Delivery Date]
VAR _count = 
COUNTROWS(
    FILTER(
        ALL('Table2'),
        'Table2'[Start]<=_date
        && 'Table2'[End]>=_date
    )
)
VAR _result = 
IF(_count>=1,1,0)
RETURN
_result

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[FreemanZ · Accepted solution]

hi @SanKing 

try to add a column in Table2 with this:

DurationCount = 
VAR _start = [Start]
VAR _end =[End]
RETURN
COUNTROWS(
    FILTER(
        Table1,
        Table1[Date]>=_start
            &&Table1[Date]<=_end
    )
)

i tried and it worked like this:

the Table1 sample:

[SanKing]

Thank you @FreemanZ ,
Works perfectly!
And is there a way to mark each ticket if it was affected?

[FreemanZ]

hi @SanKing 

you are welcom. what does that mean?

[SanKing · Accepted solution]

Hi @FreemanZ ,
I would like to mark (in new column) in Table 1 if ticket was affected, means 
IF ticket was printed in breakdown time "YES" (or 1), IF not "NO" (or 0).
Formula:
Breakdown tickets? = IF(AND('Table 1'[Ticket Delivery Date]>=MIN('Table 2'[Start]), 'Table 1'[Ticket Delivery Date]<=Max('Table 2'[End])),1,0)
Doesn't work.
Thank you in advance!

[FreemanZ]

hi @SanKing ,What do you mean by don't work? Wrong result or error?

[SanKing]

Hi @FreemanZ ,
Unfortunately the results are wrong, it's in 98% 1, exept 3 last days.

[v-cgao-msft · Accepted solution]

Hi @SanKing ,

If I understand correctly, please try this:

Breakdown tickets? = 
VAR _date = 'Table1'[Ticket Delivery Date]
VAR _count = 
COUNTROWS(
    FILTER(
        ALL('Table2'),
        'Table2'[Start]<=_date
        && 'Table2'[End]>=_date
    )
)
VAR _result = 
IF(_count>=1,1,0)
RETURN
_result

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[SanKing]

Thank you @v-cgao-msft ,
Works perfect, 
Last question - is it possible to add IF parameter for If country from Table1 = Table2?

If I have breakdown in UK, and I don't have breakdown in FR I shouldn't mark tickets from FR.

Thanks in advance!

[v-cgao-msft · Accepted solution]

Hi @SanKing ,

Like this?

Breakdown tickets? = 
VAR _date = 'Table1'[Ticket Delivery Date]
VAR _country = 'Table1'[Country]
VAR _count = 
COUNTROWS(
    FILTER(
        ALL('Table2'),
        'Table2'[Country]=_country
        && 'Table2'[Start]<=_date
        && 'Table2'[End]>=_date
    )
)
VAR _result = 
IF(_count>=1,1,0)
RETURN
_result

If I have misunderstood your needs, please feel free to contact me and preferably with expected output.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[SanKing]

Dear @v-cgao-msft ,

Thank you!

This works perfectly!