Sum of unique rows in one table using variables from a different table

[PBINewbie12]

Hello,

As my nane implies, I am new to PowerBI, but after some searching, I could not find the answer to this question.   Apologies if it is out there and I missed it!

I have a very large dataset that looks something like below. 

Requests for Assistance
Requestor	Date
Dave	2/4/2021
Suzy	2/5/2021
Dave	2/6/2021
Joe	2/7/2021
Bill	2/8/2021
Suzy	2/9/2021
Tom	2/10/2021
Frank	2/11/2021
Jolene	2/12/2021
Maggie	2/13/2021
Suzy	2/14/2021
Dave	2/15/2021
Kathy	2/16/2021

I want to be able to calculate the number of unique requests based on a table of VIP's (I don't need to know the quantity for each VIP, just how how many in this table are from VIP's.   That table looks like

VIP Requestors

Requestor

Suzy

Frank

In this example, the measure should return 4.

Appreciate your help in understanding how to do this!

[AlexisOlson · Accepted solution]

There are lots of ways to do this.

Here are a couple:

COUNTROWS (
    FILTER ( Requests, Requests[Requestor] IN VALUES ( VIPs[Requestor] ) )
)

CALCULATE (
    COUNT ( Requests[Requestor] ),
    TREATAS ( VALUES ( VIPs[Requestor] ), Requests[Requestor] )
)

[PBINewbie12]

That was super helpful Alexis!   Thank you very much.

[Whitewater100]

Hi PBINewbi:

Don't worry, in the beginning all this seems confusing. I have put together an appropriate data model (based on what you wrote) and added supporting tables. The solution is done in both a calculated column and measure.

I will attach file for your review. There's usually more than one way to get to a result.

I hope you can accept as a solution. Thanks..https://drive.google.com/file/d/1i9COJVf8XcwtYkaB6a70RpqA_vhJDQ0z/view?usp=sharing