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]
        )
    )
)
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.

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

        Thanbks for more input. 

         

        1) Both are text with just a phone number in the same format 11 digits: 48123456789

        2) The column in Values() Phone from SalesRep does not have a relation ship with RecipientNumber in the table PhoneCalls
        3) Can You show an example? 

        Thank You very much !