Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Frequent Visitor

Count the number of agent occurences per listing



Needing some assistance again please. My subject may not be an accurate description of what I am after but the scenario is this.


We are working on a number of metrics and whilst we mostly have the information available, the CRM system we are currently using has a number of dashboard and reporting capabilities, which is why we're having to resort to presenting the data via PowerBI.


I work in the commercial real estate industry and one of the reports that we are wokring on is a report that provide us with the number of active listings we have (unique key is the listing ID). For each listing, we may either have 1 or 2 agents assigned per listing. What we want to get out of this is to provide how many number of listings an agent has on a monthly basis. So for example, if we have this data set...


Listing IDAgent 1Agent 2Listing Date
123456John SmithJane Doe1 Jan 2023
654321John Smith 22 Jan 2023
234567Jane Doe 10 Feb 2023
765432Alex Smith John Smith5 Feb 2023
345678Alex Smith 6 Feb 2023

For the month of Jan, I would have 2 counts for John Smith and 1 count for Jane Doe.


For the month of Feb, I would have 1 count for Jane Doe, 2 counts for Alex Smith and 1 count for John Smith.


I may need to have a follow-up question but will leave it for later. How do we do this in PowerBI so that the number of listings are reflected accurately for each agent regardless of the agent being Agent 1 or Agent 2?










Super User
Super User

Hiu @jgulliver ,


I would unpivot Agent 1 and 2 to be able to calculate the count of listing per agent per month like below:


Please see attached pbix for the details.

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Connect to me on LinkedIn || Need consulting? Hire me for a Power BI gig on UpWork.
Learn with me on YouTube @PowerBITambayan.
Super User
Super User


I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.





Agent count: =
VAR _agentonelist =
    DISTINCT ( Data[Agent 1] )
VAR _agenttwolist =
    DISTINCT ( Data[Agent 2] )
VAR _agentalllist =
    FILTER (
            DISTINCT ( UNION ( _agentonelist, _agenttwolist ) ),
            "@agentlist", [Agent 1]
        [@agentlist] <> BLANK ()
    COUNTROWS ( _agentalllist )

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Go to My LinkedIn Page

Helpful resources

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors