Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Filter when there is no data

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

 

date  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
  • Anonymous's avatar
    Anonymous
    5 years ago

    NoActivity = 
VAR CaseTable = VALUES('Cases'[CaseID])
VAR MonthTable = VALUES('Datum'[YYYYMM])
VAR CJTable = CROSSJOIN(CaseTable,MonthTable)
VAR ActivityTable = ADDCOLUMNS(VALUES('Activities'),"YYYYMM",LOOKUPVALUE('Datum'[YYYYMM],'Datum'[Date],[  Date]))
VAR FindActivities = 
    ADDCOLUMNS(
        CJTable,
        "Activity",
            VAR CID = [CaseID]
            VAR YYMM = [YYYYMM]
            VAR FT = FILTER(ActivityTable,[  CaseID]=CID && [YYYYMM]=YYMM)
            RETURN
            CONCATENATEX(FT,[Activity Id])
    )
VAR ValidityCheckTable = 
    ADDCOLUMNS(
        FindActivities,
        "ValidityCheck",
        VAR YM = [YYYYMM]
        VAR SDOM = MINX(FILTER('Datum','Datum'[YYYYMM]=YM),'Datum'[Date])
        VAR LDOM = MAXX(FILTER('Datum','Datum'[YYYYMM]=YM),'Datum'[Date])
        VAR CSD = LOOKUPVALUE('Cases'[  start case],'Cases'[CaseID],[CaseID])
        VAR CED = LOOKUPVALUE('Cases'[  end case],'Cases'[CaseID],[CaseID])
        VAR PSD = LOOKUPVALUE('Cases'[  start prod],'Cases'[CaseID],[CaseID])
        VAR PED = LOOKUPVALUE('Cases'[  end   prod],'Cases'[CaseID],[CaseID])
        VAR CheckResult =
            IF(
                CSD<=SDOM && CED>=LDOM && PSD<=SDOM && PED>=LDOM,"Valid","Invalid")
        RETURN CheckResult
    )
VAR Result = FILTER(ValidityCheckTable,[ValidityCheck]="Valid" && [Activity]="")
RETURN
Result

     

    Note: I have created a calculated table that gives the result shown above. If you want to use this for some calculation in other measures, you could use the same code to create temporary tables inside measures.

    Change the table names and field names accordingly. This is based on your sample data.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    NoActivity = 
VAR CaseTable = VALUES('Cases'[CaseID])
VAR MonthTable = VALUES('Datum'[YYYYMM])
VAR CJTable = CROSSJOIN(CaseTable,MonthTable)
VAR ActivityTable = ADDCOLUMNS(VALUES('Activities'),"YYYYMM",LOOKUPVALUE('Datum'[YYYYMM],'Datum'[Date],[  Date]))
VAR FindActivities = 
    ADDCOLUMNS(
        CJTable,
        "Activity",
            VAR CID = [CaseID]
            VAR YYMM = [YYYYMM]
            VAR FT = FILTER(ActivityTable,[  CaseID]=CID && [YYYYMM]=YYMM)
            RETURN
            CONCATENATEX(FT,[Activity Id])
    )
VAR ValidityCheckTable = 
    ADDCOLUMNS(
        FindActivities,
        "ValidityCheck",
        VAR YM = [YYYYMM]
        VAR SDOM = MINX(FILTER('Datum','Datum'[YYYYMM]=YM),'Datum'[Date])
        VAR LDOM = MAXX(FILTER('Datum','Datum'[YYYYMM]=YM),'Datum'[Date])
        VAR CSD = LOOKUPVALUE('Cases'[  start case],'Cases'[CaseID],[CaseID])
        VAR CED = LOOKUPVALUE('Cases'[  end case],'Cases'[CaseID],[CaseID])
        VAR PSD = LOOKUPVALUE('Cases'[  start prod],'Cases'[CaseID],[CaseID])
        VAR PED = LOOKUPVALUE('Cases'[  end   prod],'Cases'[CaseID],[CaseID])
        VAR CheckResult =
            IF(
                CSD<=SDOM && CED>=LDOM && PSD<=SDOM && PED>=LDOM,"Valid","Invalid")
        RETURN CheckResult
    )
VAR Result = FILTER(ValidityCheckTable,[ValidityCheck]="Valid" && [Activity]="")
RETURN
Result

     

    Note: I have created a calculated table that gives the result shown above. If you want to use this for some calculation in other measures, you could use the same code to create temporary tables inside measures.

    Change the table names and field names accordingly. This is based on your sample data.