Forum Discussion

Fistachpl's avatar
Fistachpl
Icon for Helper III rankHelper III
1 year ago
Solved

NOT INVALUES is not working

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 bo...
INVALUES()
  • v-kathullac's avatar
    v-kathullac
    1 year ago

    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's avatar
Fistachpl
Icon for Helper III rankHelper III
1 year ago

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's avatar
johnt75
Icon for Super User rankSuper User
1 year ago

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