Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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!
Solved! Go to Solution.
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 :
Please mark if it helps.
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 :
Please mark if it helps.
Thank you! Very clear explanation
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
15 | |
11 | |
8 | |
8 | |
8 |
User | Count |
---|---|
22 | |
13 | |
11 | |
10 | |
10 |