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.
Hi all,
did there still exist a function like "not in"?
I try to find values which are maybe in the one table but not in another one.
could someone help?
Thanks.
Hi Sebastian,
you can try the EXCEPT function ( https://msdn.microsoft.com/en-us/library/mt243784.aspx ), but watch out because the two tables must have the same dimension.
Have a good coding
Hey. Thanks for your answer.
The except function doesn't work.
My problem:
I have a table (table 1) which consist of all customer information and a second table (table 2) which consist of sales information.
I try to find all customer who are in table 1 but not in table 2.
You might want to solve this problem in "M" versus DAX. You can control the way "M" joins things.
http://blog.crossjoin.co.uk/2014/06/02/join-conditions-in-power-query-part-1/
As a query:
EVALUATE( CALCULATETABLE( DimCustomer ) ,EXCEPT( VALUES( DimCustomer[CustomerKey] ) ,VALUES( FactSale[CustomerKey] ) ) )
As a measure you can use in a visualization:
Customers without Sales = COUNTROWS( CALCULATETABLE( DimCustomer ,EXCEPT( VALUES( DimCustomer[CustomerKey] ) ,VALUES( FactSale[CustomerKey] ) ) ) )
You could use anything in a CALCULATE() with that EXCEPT() as a filter, but I think COUNTROWS() of the resulting DimCustomer is appropriate, because you could use a customer hierarchy and see how many customers in various groups have no purchases. This will also work with filters on your date dimension, so you could filter to a specific date and see customers without sales on that date.
@greggyb wrote:As a query:
EVALUATE( CALCULATETABLE( DimCustomer ) ,EXCEPT( VALUES( DimCustomer[CustomerKey] ) ,VALUES( FactSale[CustomerKey] ) ) )As a measure you can use in a visualization:
Customers without Sales = COUNTROWS( CALCULATETABLE( DimCustomer ,EXCEPT( VALUES( DimCustomer[CustomerKey] ) ,VALUES( FactSale[CustomerKey] ) ) ) )You could use anything in a CALCULATE() with that EXCEPT() as a filter, but I think COUNTROWS() of the resulting DimCustomer is appropriate, because you could use a customer hierarchy and see how many customers in various groups have no purchases. This will also work with filters on your date dimension, so you could filter to a specific date and see customers without sales on that date.
What if its two different tables, with different columns, but has a relationship on one of the columns?
Lets say TableA has column, A1, B1,C1,D1,E1,F1 and TableB has column A2,B2,C2
Normally I would do Select COUNT(*) from table A where A1 not in (select A2 from TableB) but with your measure it says "Each table argument of 'EXCEPT' must have the same number of columns?
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 |