NOT INVALUES is not working

[Fistachpl]

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!

[techies]

Hi @Fistachpl please try this measure

measure=

CALCULATE (

    SUM ( 'phone length'[CallLength] ),

    FILTER (

        'phone length',

        NOT CONTAINS (

            SalesReps,

            SalesReps[Phone],

            'phone length'[RecipientNo]

        )

    )

)

[techies]

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"

)

[Fistachpl]

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()

[v-kathullac]

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.

[Fistachpl]

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 !

[v-kathullac · Accepted solution]

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])
)

2)

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

Regards,

Chaithanya.

[Fistachpl]

Does not work 😞 

[johnt75]

Try

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

[Fistachpl]

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

[johnt75]

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

[Fistachpl]

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

[pravinW007]

Can share your pbix file with sample expected output.

Thanks,
Pravin Wattamwar

[Fistachpl]

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.

[johnt75]

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

[Fistachpl]

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

[johnt75]

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.

[Fistachpl]

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