Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.
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

Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City


Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors