Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
So I have two tables as below that have a relationship with OPPID between them
OPPORTUNITY
OPPID - STATUS
OPP1 - OPEN
OPP2 - CLOSE
OPP3 - OPEN
OPP4 - OPEN
ACTIVITY
OPPID - ACTIVITY - DATE
OPP1 - ACT1 - 1/1/2017
OPP1 - ACT2 - 1/1/2018
OPP3 - ACT3 - 3/1/2017
So Opportunity table lists all the opportunities and activity table lists all the activities for opporutunities
Now I need a list of ALL "OPEN" Opportunities that do not any activities as well as those that do not have any future acitivites
So the LOGIC is
Show all OPPID where STATUS = OPEN
and
OPPID does not exist in ACTIVITY TABLE
or
OPPID exists but has a DATE < NOW()
Solved! Go to Solution.
@moizsherwani wrote:
So I have two tables as below that have a relationship with OPPID between them
OPPORTUNITY
OPPID - STATUS
OPP1 - OPEN
OPP2 - CLOSE
OPP3 - OPEN
OPP4 - OPEN
ACTIVITY
OPPID - ACTIVITY - DATE
OPP1 - ACT1 - 1/1/2017
OPP1 - ACT2 - 1/1/2018
OPP3 - ACT3 - 3/1/2017
So Opportunity table lists all the opportunities and activity table lists all the activities for opporutunities
Now I need a list of ALL "OPEN" Opportunities that do not any activities as well as those that do not have any future acitivites
So the LOGIC is
Show all OPPID where STATUS = OPEN
and
OPPID does not exist in ACTIVITY TABLE
or
OPPID exists but has a DATE < NOW()
So we can say the filter is STATUS= OPEN and NOT EXIST ACTIVITY DATE>=NOW(), right?
Try
New table = FILTER ( OPPORTUNITY, NOT ( CONTAINS ( FILTER ( ACTIVITY, ACTIVITY[DATE] >= NOW () ), ACTIVITY[OPPID], OPPORTUNITY[OPPID] ) ) && OPPORTUNITY[STATUS] = "OPEN" )
Then create a calculated column as
ShowOrHidden = IF ( MAXX ( RELATEDTABLE ( ACTIVITY ), ACTIVITY[DATE] ) < NOW () && OPPORTUNITY[STATUS] = "OPEN", "SHOW", "HIDDEN" )
RELATEDTABLE is used, as I think there's a proper one to many relationship between those two tables.
@moizsherwani wrote:
So I have two tables as below that have a relationship with OPPID between them
OPPORTUNITY
OPPID - STATUS
OPP1 - OPEN
OPP2 - CLOSE
OPP3 - OPEN
OPP4 - OPEN
ACTIVITY
OPPID - ACTIVITY - DATE
OPP1 - ACT1 - 1/1/2017
OPP1 - ACT2 - 1/1/2018
OPP3 - ACT3 - 3/1/2017
So Opportunity table lists all the opportunities and activity table lists all the activities for opporutunities
Now I need a list of ALL "OPEN" Opportunities that do not any activities as well as those that do not have any future acitivites
So the LOGIC is
Show all OPPID where STATUS = OPEN
and
OPPID does not exist in ACTIVITY TABLE
or
OPPID exists but has a DATE < NOW()
So we can say the filter is STATUS= OPEN and NOT EXIST ACTIVITY DATE>=NOW(), right?
Try
New table = FILTER ( OPPORTUNITY, NOT ( CONTAINS ( FILTER ( ACTIVITY, ACTIVITY[DATE] >= NOW () ), ACTIVITY[OPPID], OPPORTUNITY[OPPID] ) ) && OPPORTUNITY[STATUS] = "OPEN" )
@Eric_Zhang, this definetely works as a solution, I would like to have added as a column to the OPPORTUNITY table with the "Show", "Hide" meaning if the Opp meets all criteria (i.e. exists in your new table) then it would be "show" else "hide"
Then create a calculated column as
ShowOrHidden = IF ( MAXX ( RELATEDTABLE ( ACTIVITY ), ACTIVITY[DATE] ) < NOW () && OPPORTUNITY[STATUS] = "OPEN", "SHOW", "HIDDEN" )
RELATEDTABLE is used, as I think there's a proper one to many relationship between those two tables.
Why the Maxx function? Please note there are some activity with no date as well so those are also not considered future activities, will this formula consider those. Thanks
@moizsherwani wrote:
Why the Maxx function? Please note there are some activity with no date as well so those are also not considered future activities, will this formula consider those. Thanks
Even with no date, the expression MAXX ( RELATEDTABLE ( ACTIVITY ), ACTIVITY[DATE] ) < NOW () would return True, right? I think the DAX can be applied to you case, as we have confirmed that the condition is STATUS= OPEN and NOT EXIST ACTIVITY DATE>=NOW(), namely the max date ACTIVITY <NOW().
Have your tried and what's going on?
@Eric_Zhang, Yes the solution seems to be correct but since I wanted to verify that this was taken in account. Can you explain the logic of what the formula is doing for my understanding?
Hi again @moizsherwani
Does this Calculated Table get close to what you are after?
New Table = VAR T1 = SELECTCOLUMNS(FILTER('OPPORTUNITY','OPPORTUNITY'[STATUS]="OPEN"),"OPPID",[OPPID]) VAR T2 = SELECTCOLUMNS(FILTER('ACTIVITY','ACTIVITY'[DATE]>TODAY()),"OPPID",[OPPID]) RETURN EXCEPT(T1,T2)
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |