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

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

pravinW007's avatar
pravinW007
Regular Visitor
1 year ago

Can share your pbix file with sample expected output.

Thanks,
Pravin Wattamwar

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

        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