Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

filter when there is no value

I've got 3 tables

 

CaseID  start case  end case  start prod  end prod
1  1-1-2020  31-7-2020  1-1-2020  31-5-2020
2  1-2-2020  30-6-2020  1-3-2020  30-4-2020

 

Activity Id  CaseID  Date
1  1  7-1-2020
2  1  3-2-2020
3  1  4-4-2020
4  1  5-5-2020
5  1  6-6-2020
6  2  2-3-2020

 

datum  YYYY-MM
1-1-2020  2020-1
2-1-2020  2020-1
3-1-2020  2020-1
etc. 

 

What I want to know is in which months there wasn't an activity while the month is valid. That means its between the start en end of the case AND between the start en end of a product. 

 

In this example I want the endresult to be:

Case Id  YYYY-MM
1  2020-3
2  2020-4

 

It's also possible to select a filter on YYYY-MM and that only the caseID's will apear when there is no activity in a valid period. Does anyone have an idea?

Need Help
  • v-alq-msft's avatar
    v-alq-msft
    5 years ago

    Hi, Anonymous 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

    Table1:

    Table2:

    Calendar(a calculated table):

    Calendar = 
ADDCOLUMNS(
    CALENDARAUTO(),
    "YYYY-MM",
    FORMAT([Date],"yyyy-MM")
)

     

    Relationship:

     

    You may create a measure as below.

    Result = 
var tab = 
ADDCOLUMNS(
    Table1,
    "Re",
    var caseid = [CaseID]
    var s = [Start Prod]
    var e = [End Prod]
    var t1 = 
    CALCULATETABLE(
        DISTINCT('Calendar'[YYYY-MM]),
        FILTER(
            ALL('Calendar'),
            [Date]>=s&&
            [Date]<=e
        )
    )
    var t2 = 
    SELECTCOLUMNS(
            ADDCOLUMNS(
                FILTER(
                   ALL(Table2),
                   [CaseID]=caseid
                ),
                "YM",
                FORMAT([Date],"yyyy-MM")
            ),
            "YM",
            [YM]
    )
    return
    CONCATENATEX(
        FILTER(
            t1,
            NOT([YYYY-MM] in t2)
        ),
        [YYYY-MM],
        ","
    )
)
return
CONCATENATEX(
    tab,
    [Re],
    ","
)

     

    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.

2 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    5 years ago

    Use CALENDAR() to create arrays of days where each of the cases was active. Then INTERSECT that with the dates from your calendar table.  A rowcount of 0 means there was no case during that month etc.

     

    By the way, YYYY-MM does not produce the format you indicate.

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    5 years ago

    Hi, Anonymous 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

    Table1:

    Table2:

    Calendar(a calculated table):

    Calendar = 
ADDCOLUMNS(
    CALENDARAUTO(),
    "YYYY-MM",
    FORMAT([Date],"yyyy-MM")
)

     

    Relationship:

     

    You may create a measure as below.

    Result = 
var tab = 
ADDCOLUMNS(
    Table1,
    "Re",
    var caseid = [CaseID]
    var s = [Start Prod]
    var e = [End Prod]
    var t1 = 
    CALCULATETABLE(
        DISTINCT('Calendar'[YYYY-MM]),
        FILTER(
            ALL('Calendar'),
            [Date]>=s&&
            [Date]<=e
        )
    )
    var t2 = 
    SELECTCOLUMNS(
            ADDCOLUMNS(
                FILTER(
                   ALL(Table2),
                   [CaseID]=caseid
                ),
                "YM",
                FORMAT([Date],"yyyy-MM")
            ),
            "YM",
            [YM]
    )
    return
    CONCATENATEX(
        FILTER(
            t1,
            NOT([YYYY-MM] in t2)
        ),
        [YYYY-MM],
        ","
    )
)
return
CONCATENATEX(
    tab,
    [Re],
    ","
)

     

    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.