Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
OPDRACHTID | factuurID | Research activities |
2018-0004-001 | Authorize report | |
2018-0004-001 | Authorize task | |
2018-0004-001 | Report process control | |
2018-0004-001 | 43246 | Periodic control |
2018-0004-001 | 43246 | Test article |
2018-0004-002 | Authorize report | |
2018-0004-002 | Authorize task | |
2018-0004-002 | Report process control | |
2018-0004-002 | 43246 | Periodic 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-004 | 43846 | Periodic 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-006 | 44902 | Periodic control |
2018-0004-006 | 44902 | Control 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!
Solved! Go to Solution.
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 )
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 )
File attached as well
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |