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.

techies's avatar
techies
Icon for Super User rankSuper User
1 year ago

Hi Fistachpl please try this measure

 

measure=
CALCULATE (
    SUM ( 'phone length'[CallLength] ),
    FILTER (
        'phone length',
        NOT CONTAINS (
            SalesReps,
            SalesReps[Phone],
            'phone length'[RecipientNo]
        )
    )
)
  • Fistachpl's avatar
    Fistachpl
    Icon for Helper III rankHelper III
    1 year ago

    Does not work 😞 

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

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

      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's avatar
        v-kathullac
        Icon for Community Support rankCommunity Support
        1 year ago

        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.