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.

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

      • johnt75's avatar
        johnt75
        Icon for Super User rankSuper User
        1 year ago

        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.