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
Mark88
Frequent Visitor

Exclude values using DAX

Can you please advise how to solve the following issue: We have several Research activities that are part of the same 'Opdracht', so they have the same OPDRACHTID. Some activities are already invoiced and the ones that are invoiced have been marked with a factuurID. Now I want to make a report of the OPDRACHTID's containing Research activities that have never been invoiced. So when an OPDRACHTID contains only one activity that has been invoiced, I want to exclude the whole OPDRACHTID from the list. 

 

See below example:

 

OPDRACHTIDfactuurIDResearch activities
2018-0004-001 Authorize report
2018-0004-001 Authorize task
2018-0004-001 Report process control
2018-0004-00143246Periodic control
2018-0004-00143246Test article
2018-0004-002 Authorize report
2018-0004-002 Authorize task
2018-0004-002 Report process control
2018-0004-00243246Periodic control
2018-0004-003 Autorize report
2018-0004-003 Report process control
2018-0004-004 Authorize report
2018-0004-004 Authorize task
2018-0004-004 Report process control
2018-0004-00443846Periodic control
2018-0004-005 Authorize report
2018-0004-005 Authorize task
2018-0004-005 Report process control
2018-0004-005 Report process control
2018-0004-006 Authorize report
2018-0004-006 Authorize task
2018-0004-006 Report process control
2018-0004-00644902Periodic control
2018-0004-00644902Control article

 

So from the above example I want to maintain the bold marked OPDRACHTID's in my report. All other OPDRACHTID's have at least one Research activity that has been invoiced (factuurID). Any advise on how I can achieve this would be much appreciated!

1 ACCEPTED SOLUTION
Zubair_Muhammad
Community Champion
Community Champion

@Mark88

 

Try this calculated table

From Modelling tab>>>NEw Table

 

Calculated Table =
VAR IDs_with_factur =
    CALCULATETABLE (
        VALUES ( Table1[OPDRACHTID] ),
        FILTER ( Table1, Table1[factuurID] <> BLANK () )
    )
VAR IDs_without_factur =
    EXCEPT ( VALUES ( Table1[OPDRACHTID] ), IDs_with_factur )
RETURN
    FILTER ( Table1, Table1[OPDRACHTID] IN IDs_without_factur )

Regards
Zubair

Please try my custom visuals

View solution in original post

3 REPLIES 3
Zubair_Muhammad
Community Champion
Community Champion

@Mark88

 

Try this calculated table

From Modelling tab>>>NEw Table

 

Calculated Table =
VAR IDs_with_factur =
    CALCULATETABLE (
        VALUES ( Table1[OPDRACHTID] ),
        FILTER ( Table1, Table1[factuurID] <> BLANK () )
    )
VAR IDs_without_factur =
    EXCEPT ( VALUES ( Table1[OPDRACHTID] ), IDs_with_factur )
RETURN
    FILTER ( Table1, Table1[OPDRACHTID] IN IDs_without_factur )

Regards
Zubair

Please try my custom visuals

Thanks very much @Zubair_Muhammad!! This solved my problem.

@Mark88

 

File attached as well

 

exclude values using DAX.png


Regards
Zubair

Please try my custom visuals

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.