Forum Discussion

CarlsBerg999's avatar
CarlsBerg999
Icon for Helper V rankHelper V
3 years ago
Solved

applying date filters to a calculated table (subset)

Hi,

 

My table looks like the following:

Transaction ID Account ID Attribute mod. Value € Value
70345 320664 Lost 4 200,00 25/08/2022
70345 320664 New  4 200,00 05/05/2021
70345 320664 Sourced 4 200,00  
80142 340033 Lost 5 400,00 15/02/2021
80142 340033 Conference 5 400,00 19/08/2020
80142 340033 New  5 400,00 01/01/2020
80142 340033 Sourced 5 400,00  
91100 320664 Lost 4 200,00 22/03/2021
91100 320664 New 4 200,00 05/01/2021
91100 320664 Sourced 4 200,00  

 

What i need to do, is to count how many Transaction ID's have been lost during a selected period. However, the transaction should not have specfic items in the Attribute Mod. -column. Assuming the selected date is 31.12.2022 the table above should only return transaction ID 70345 because it is lost during 2022 but it does not have a Conference, Called or Sourced -attributes associated with it. 

 

My Measure is presented below. My problem is with the dates. The variables return correct dates but the RETURN -part of the measure ignores the date restrictions. The other restrictions work fine. 

How do i apply a filter to the measure below, so that the Lost date must be between 1.1.2022 and 31.12.2022? 

 

 

VAR Selected_Date_less_365 = [Funnel: Selected Date (EOMONTH)]-365 //This takes 31.12.2022 - 365
VAR Selected_Date1 = [Funnel: Selected Date (EOMONTH)] // is 31.12.2022

RETURN
COUNTROWS(
    CALCULATETABLE(
        FILTER(
            CALCULATETABLE(VALUES(Table[Transaction ID])),
                    ISEMPTY(CALCULATETABLE('Table',
                    'Table'[Attribute mod.]="Conference"))
                &&
                    ISEMPTY(CALCULATETABLE('Table',
                    'Table'[Attribute mod.]="Sourced"))
                &&
                    ISEMPTY(CALCULATETABLE('Table',
                    'Table'[Attribute mod.]="Called"))
                &&
                    NOT ISEMPTY(CALCULATETABLE('Table',
                    'Table'[Attribute mod.]="Lost"))
                &&
                    NOT ISEMPTY(CALCULATETABLE('Table',
                    Table[Value]>Selected_Date_less_365,
                    Table[Value]<=Selected_Date1,
                    FILTER(Table,Table[Earliest Signed Contract]>Selected_Date_less_365),
                    FILTER(Table,Table[Earliest Signed Contract]<=Selected_Date1)))),
        CROSSFILTER(DateTable[Date],'Related_Table'[End Date],none)))

 

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    CarlsBerg999 I would do it this way, PBIX is attached below signature.

    Measure = 
    VAR __Date = MAX('Dates'[Date])
    VAR __BeginDate = DATE(YEAR(__Date),1,1)
    VAR __Table = FILTER(ALL('Table'),[Value]>=__BeginDate && [Value]<=__Date)
    VAR __LostIDs = DISTINCT(SELECTCOLUMNS(FILTER(__Table,[Attribute mod.] = "Lost"),"__TransactionID",[Transaction ID]))
    VAR __OtherIDs = DISTINCT(SELECTCOLUMNS(FILTER(__Table,[Attribute mod.] = "Conference" || [Attribute mod.] = "Sourced" || [Attribute mod.] = "Called"),"__TransactionID",[Transaction ID]))
    VAR __Result = COUNTROWS(EXCEPT(__LostIDs, __otherIDs))
RETURN
    __Result

     

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    3 years ago

    CarlsBerg999 I would do it this way, PBIX is attached below signature.

    Measure = 
    VAR __Date = MAX('Dates'[Date])
    VAR __BeginDate = DATE(YEAR(__Date),1,1)
    VAR __Table = FILTER(ALL('Table'),[Value]>=__BeginDate && [Value]<=__Date)
    VAR __LostIDs = DISTINCT(SELECTCOLUMNS(FILTER(__Table,[Attribute mod.] = "Lost"),"__TransactionID",[Transaction ID]))
    VAR __OtherIDs = DISTINCT(SELECTCOLUMNS(FILTER(__Table,[Attribute mod.] = "Conference" || [Attribute mod.] = "Sourced" || [Attribute mod.] = "Called"),"__TransactionID",[Transaction ID]))
    VAR __Result = COUNTROWS(EXCEPT(__LostIDs, __otherIDs))
RETURN
    __Result

     

    • CarlsBerg999's avatar
      CarlsBerg999
      Icon for Helper V rankHelper V
      3 years ago

      Hi,

       

      Thank you! This is very close, but this loses one important functionality, unless you can solve it: Can you figure out a way in which we can add multiple conditions to left table (Lost IDs)? In other words, I need to also calculate how many transactions were Lost despite being Called. 

      As count rows that: 

      [Attribute mod.] = "Lost"

      &&

      [Attribute mod.] = "Called"


      But exclude the others listed in left table (Conference, Sourced). Because the data is in the same column, i can't just add a second condition to the filter