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...
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

     

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