Forum Discussion
Helper IIINOT INVALUES is not working
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.
Helper IIIHello,
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.
Super UserYou 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- johnt75
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
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 - Fistachpl
Of course - I forgot about this relationship I created while trying to achive the result 🙂