topic Re: NOT INVALUES is not working in DAX Commands and Tips

NOT INVALUES is not working

Fistachpl — Mon, 19 May 2025 11:01:05 GMT

I have two tables:

SalesReps with Columns Name, Phone
Jan Kowalski, 48123456789

and PhoneLogs with Columns
Caller, RecipientNo, CallLength and some other columns

I have relation between both tables: *:1 PhoneLogs[Caller] to SalesReps[Name]

Now I want to calculate the length of all calls that were not made to other Sales representatives (RecipientNo does not exist in SalesReps[Phone] and display it in table visual (not exact formatting but You will get an idea):

_callLenght = calculate( sumx(PhoneLogs, PhoneLogs[CallLength]), FILTER( PhoneLogs, NOT PhoneLogs[RecipientNo] IN VALUES(SalesReps[Phone]) ) )

But this measure still calculates sum of length of all calls...

Where is an error in my way of thinking?

Thanks in advance!

Re: NOT INVALUES is not working

johnt75 — Mon, 19 May 2025 12:25:53 GMT

Try

_callLength = VAR SalesRepNumbers = VALUES ( SalesReps[Phone] ) VAR Result = CALCULATE ( SUMX ( PhoneLogs, PhoneLogs[CallLength] ), NOT PhoneLogs[RecipientNo] IN SalesRepNumbers ) RETURN Result

Re: NOT INVALUES is not working

Fistachpl — Mon, 19 May 2025 13:59:55 GMT

It does not work, but I am not suprised as it is basically the same syntax I tried 😕

Re: NOT INVALUES is not working

johnt75 — Mon, 19 May 2025 14:03:06 GMT

What columns from which tables do you have in the table visual ?

Re: NOT INVALUES is not working

Fistachpl — Mon, 19 May 2025 18:02:12 GMT

I have Name from table SalesReps and for those I want to calculate a measure.

Re: NOT INVALUES is not working

techies — Mon, 19 May 2025 19:08:37 GMT

Hi Fistachpl please try this measure

measure=

CALCULATE (

SUM ( 'phone length'[CallLength] ),

FILTER (

'phone length',

NOT CONTAINS (

SalesReps,

SalesReps[Phone],

'phone length'[RecipientNo]

)

Re: NOT INVALUES is not working

Fistachpl — Tue, 20 May 2025 05:23:29 GMT

Does not work 😞

Re: NOT INVALUES is not working

pravinW007 — Tue, 20 May 2025 06:02:21 GMT

Can share your pbix file with sample expected output.

Thanks,
Pravin Wattamwar

Re: NOT INVALUES is not working

Fistachpl — Tue, 20 May 2025 06:41:58 GMT

Hello,

no problem take a look here:

Phone calls of Sales Reps.pbix

Please note that it calculates the total sum correctly, but sums for each sales rep are incorrect.

Re: NOT INVALUES is not working

techies — Tue, 20 May 2025 08:22:47 GMT

Hi Fistachpl based on the pbix file shared , you can create a calculated column first to check the sales rep's yes/no

IsRecipientSalesRep =

IF (

ISBLANK (

LOOKUPVALUE (

SalesReps[Name],

SalesReps[Phone],

PhoneLogs[Recipient]

)

"No",

"Yes"

)

and then measures as this

TotalLength NonSalesReps =

CALCULATE (

SUM ( PhoneLogs[Length]),

PhoneLogs[IsRecipientSalesRep] = "No"

)

TotalLength SalesReps =

CALCULATE (

SUM ( PhoneLogs[Length] ),

PhoneLogs[IsRecipientSalesRep] = "Yes"

)

Re: NOT INVALUES is not working

Fistachpl — Tue, 20 May 2025 08:42:52 GMT

Yes this I know but I did not use the Yes/No but merged two queries and have the name of the recipient (if he is the sales representative). This way I can calculate how many times, each of the Sales Rep called other sales reps - just gives more options. My question here was more to check why it does not calculate using IN VALUES()

Re: NOT INVALUES is not working

johnt75 — Tue, 20 May 2025 09:11:38 GMT

You need to use removefilters when calculating the reps numbers

_callLength = VAR SalesRepNumbers = CALCULATETABLE( VALUES ( SalesReps[Phone] ), REMOVEFILTERS() ) VAR Result = CALCULATE ( SUMX ( PhoneLogs, PhoneLogs[Length] ), NOT PhoneLogs[Recipient] IN SalesRepNumbers ) RETURN Result

Re: NOT INVALUES is not working

v-kathullac — Tue, 20 May 2025 11:51:15 GMT

Hi Fistachpl ,

Thank you for reaching out to Microsoft Fabric Community Forum.

Below are the points why in values is not working.

1.IN VALUES() requires both columns to be comparable (same data type and format) — check that the [Recipient] column and the column you're comparing from VALUES() are exactly the same type (e.g., text without extra spaces).

2.If the column in VALUES() comes from a different or merged table and there’s no relationship between that and your main table, IN VALUES() will return nothing.

3.You can use TREATAS() to apply the list of values from one table as a filter on the other

Regards,

Chaithanya.

Re: NOT INVALUES is not working

Fistachpl — Tue, 20 May 2025 12:20:09 GMT

Thanbks for more input.

1) Both are text with just a phone number in the same format 11 digits: 48123456789

2) The column in Values() Phone from SalesRep does not have a relation ship with RecipientNumber in the table PhoneCalls
3) Can You show an example?

Thank You very much !

Re: NOT INVALUES is not working

Fistachpl — Tue, 20 May 2025 12:24:56 GMT

In my file:

From table:

CALCULATETABLE( VALUES ( SalesReps[Phone] ), REMOVEFILTERS() )

gives result (in DAX query editor):

Why is there blank in row 4 whereas my table has only 3 rows?

Thank You

Re: NOT INVALUES is not working

johnt75 — Tue, 20 May 2025 12:58:45 GMT

There is a one-to-many relationship from SalesReps to PhoneLogs, and there are entries in PhoneLogs[Recipient] which do not appear in SalesReps[Phone], so Power BI automatically creates a blank row in SalesReps, on the one-side of the relationship.

When summarizing by any column from SalesReps, and using the inactive relationship, any values from PhoneLogs which don't match a phone number would be grouped under the blank row.

Re: NOT INVALUES is not working

Fistachpl — Tue, 20 May 2025 13:02:10 GMT

Of course - I forgot about this relationship I created while trying to achive the result 🙂

Re: NOT INVALUES is not working

v-kathullac — Wed, 21 May 2025 13:21:52 GMT

Hi Fistachpl ,

Thank you for reaching out to Microsoft Fabric Community Forum.

here can calculate how many times each Sales Rep called another Sales Rep based on phone number here i have used TREATAS & LOOKUPVALUE function.

CallsToSalesReps :=
CALCULATE(
COUNTROWS(PhoneCalls),
TREATAS(VALUES(SalesRep[Phone]), PhoneCalls[RecipientNumber])
)

SalesRepName =
LOOKUPVALUE(
SalesRep[Name],
SalesRep[Phone], PhoneCalls[RecipientNumber]
)

Regards,

Chaithanya.