Except much like its SQL equivalent is meant to be used when you want to exclude certain rows from within the result set. Basically everything from Left table which doesn’t already exist in Right table. This is a common requirement in Business Intelligence a simple example would be to show a list of all customers except those who already have a credit card.
In such cases we have two version of the same table against which we are performing the calculations. Let’s say we have to provide a refund. We need to find all customers who have purchased the products and payment method they used. Follow this up by identifying those who paid by Payment Method = Unknown with amount greater <100 and exclude them from the final output.
The steps would be to identify orders made using payment method check and the amount is lesser 100.
Exclude them from the total rows in the table Transactions.
The final result is shown below
The formula used to arrive at ExExcept is shown below
ExExcept =
COUNTX(
EXCEPT(
'Fact Transaction',
CALCULATETABLE('Fact Transaction','Fact Transaction'[Payment Method Key]=0,'Fact Transaction'[Total Including Tax]<100)
)
,DISTINCTCOUNT('Fact Transaction'[Customer Key])
)
The SQL Equivalent for the above function would be similar to
SELECT *
FROM
ORDER
WHERE orderId NOT IN (
SELECT ordered
FROM
ORDER
WHERE orderamount < 100
)
Please Consider Subscribing
