cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Dynamic NOT IN list of values

Hi,

I'm new to DAX and have diffuculties with simple sintaxis.

What I am struggling to achieve is to make a dynamic list of Contacts that are not yet covered by one of the clients

Example:

ClientName     Contacts

A                      C1

A                      C2

B                      C2

B                      C4

C                      C3

D                      C4

 

In my report I would like to select ClientName 'A' from the slicer and then the table below would give me all Contacts that are not yet covered by ClientName 'A'.   Which would be 'C3' and 'C4'.

 

In SQL it would be :

SELECT

Contacts

FROM

 TableA

WHERE

 Contacts not in(SELECT Contacts FROM TableA WHERE ClientName='A')

 

Tried this as a reference : https://community.powerbi.com/t5/Desktop/Use-a-measure-to-check-a-column-for-an-array-list-of-values...

But ran into troubles defining my own list.

Any help would be greatly appreciated!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
M_L Frequent Visitor
Frequent Visitor

Re: Dynamic NOT IN list of values

Hi, this measure should list the contract ID not used by the current client by using EXCEPT on 2 columns (All contracts and contracts of the current client)

 

Measure contracts NOT used by current Client = 
VAR __AllContracts = DISTINCT(ALL(TableClients[Contracts]))
VAR __Currentclient = if(HASONEVALUE(TableClients[ClientName]);VALUES(TableClients[ClientName]))
VAR __ContractsCurrenctClient = SELECTCOLUMNS(FILTER(ALL(TableClients);TableClients[ClientName]=__Currentclient);"Contracts";[Contracts])
VAR __ContractsNotCurrenctClient = EXCEPT(__AllContracts;__ContractsCurrenctClient)
VAR __Results= CONCATENATEX(DISTINCT(__ContractsNotCurrenctClient);[Contracts];"-")
RETURN __Results

 

Results are shown below : 

20191116 v01.JPG

Please mark if it helps. 

View solution in original post

2 REPLIES 2
Highlighted
M_L Frequent Visitor
Frequent Visitor

Re: Dynamic NOT IN list of values

Hi, this measure should list the contract ID not used by the current client by using EXCEPT on 2 columns (All contracts and contracts of the current client)

 

Measure contracts NOT used by current Client = 
VAR __AllContracts = DISTINCT(ALL(TableClients[Contracts]))
VAR __Currentclient = if(HASONEVALUE(TableClients[ClientName]);VALUES(TableClients[ClientName]))
VAR __ContractsCurrenctClient = SELECTCOLUMNS(FILTER(ALL(TableClients);TableClients[ClientName]=__Currentclient);"Contracts";[Contracts])
VAR __ContractsNotCurrenctClient = EXCEPT(__AllContracts;__ContractsCurrenctClient)
VAR __Results= CONCATENATEX(DISTINCT(__ContractsNotCurrenctClient);[Contracts];"-")
RETURN __Results

 

Results are shown below : 

20191116 v01.JPG

Please mark if it helps. 

View solution in original post

Anonymous
Not applicable

Re: Dynamic NOT IN list of values

Thank you! Very clear explanation

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)