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,
Newbie here needing help with DAX for comparing 2 columns (in different tables) to get list of missing values(text). Can anyone point me in the right direction? Thanks in advance!
For example,
Table1_CustomerList
CustID
101
102
103
104
105
Table2_OrderList
CustID OrderID
102 P312
103 P451
105 P231
Output Required:
CustID (with no orders)
101
104
Solved! Go to Solution.
Hi @Anonymous ,
You could try the formula below.
Table 2 = CALCULATETABLE('Table',NOT('Table'[CustID] in VALUES('Table 1'[CustID])))
Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous
Try this (assumes no relationships between the 2 tables)
1. Place Table1[CustID] in the rows of a table visual
2. Create this measure:
ShowMeasure = VAR CurrentID = SELECTEDVALUE ( Table1[CustID] ) RETURN IF ( CALCULATE ( COUNT ( Table2[CustID] ), Table2[CustID] = CurrentID ) + 0 = 0, 1, 0 )
3. Place [ShowMeasure] as a visual filter and select to show when value is 1
Please mark the question solved when done and consider giving kudos if posts are helpful.
Cheers
Thanks!
Would we be able to get it as a data table instead of a table visual so that we can use the list of CustID without orders for some other DAX measures?
Hi @Anonymous ,
You could try the formula below.
Table 2 = CALCULATETABLE('Table',NOT('Table'[CustID] in VALUES('Table 1'[CustID])))
Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
Can you find the missing values based on two critiera? First, it needs to match with the date, then match with a vaue?
Derek
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 |
---|---|
113 | |
100 | |
78 | |
76 | |
52 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |