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.

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.

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

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