Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
moizsherwani
Continued Contributor
Continued Contributor

Compare two tables and get values that do not meet criteria

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()

 

 

Thanks,

Moiz
Was I able to answer your question? Mark my post as a solution to help others. Kudos if you liked the solution.
2 ACCEPTED SOLUTIONS
Eric_Zhang
Employee
Employee


@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"
)

 

View solution in original post

@moizsherwani

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.

 

Capture.PNG

 

View solution in original post

8 REPLIES 8
Eric_Zhang
Employee
Employee


@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"

Thanks,

Moiz
Was I able to answer your question? Mark my post as a solution to help others. Kudos if you liked the solution.

@moizsherwani

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.

 

Capture.PNG

 

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

Thanks,

Moiz
Was I able to answer your question? Mark my post as a solution to help others. Kudos if you liked the solution.


@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


@moizsherwani

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?

Thanks,

Moiz
Was I able to answer your question? Mark my post as a solution to help others. Kudos if you liked the solution.

@moizsherwani

 

I've edited my previous reply and explain the logic.

Phil_Seamark
Employee
Employee

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)

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.