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

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

Reply
Mmerinen
New Member

Remove Matrix context from a user count measure

Hi, new to PowerBi and looking for a solution to remove Matrix context from a measure.

I am creating a matrix showcasing revenue in time per product. Some product have a price per user, and others have a price per company. In the matrix I want to show all prices on user level a.k.a. i need to divide company prices with a dynamic user count. User count changes over time, and need to depict the correct usercount when time slicer is used. Below is the code i am using to show the user count of a company. Now, i cannot use this code to divide the company prices, since when i put that measure in a matrix, it will show user count as 1, on user hiearchy. How should i approach this? Any advice?

DAX FOR USER COUNT (not anchored on company level)

Users MRR Total =
VAR currentdate = MAX('MRR Calendar'[Date])

RETURN
    CALCULATE(
        DISTINCTCOUNT('MRR Clients'[user_id]),
           
        FILTER(
            ALLSELECTED('MRR Calendar'[Date]),
            ISONORAFTER('MRR Calendar'[Date], currentdate, DESC)),
       
        KEEPFILTERS(
                NOT(ISBLANK('MRR Clients'[user_id])) &&
                NOT(ISBLANK('MRR Clients'[company_billing_startdate])) &&
                'MRR Clients'[company_billing_startdate] <= currentdate &&
                'MRR Clients'[user_createdat] <= currentdate &&
                ('MRR Clients'[user_deletedat] > currentdate || ISBLANK('MRR Clients'[user_deletedat])) &&
                'MRR Clients'[company_createdat] <= currentdate &&
                ('MRR Clients'[company_deletedat] > currentdate || ISBLANK('MRR Clients'[company_deletedat])))

    )
3 REPLIES 3
Mmerinen
New Member

For the dynamic user count, the DAX below almost works, but it is not taking into account the overriding of slicer context for dates (shown in bold cursive)

Dynamic User Count =
VAR currentdate = MAX('MRR Calendar'[Date])
VAR currentcompany = SELECTEDVALUE('MRR Clients'[company_id])
RETURN

CALCULATE(
    DISTINCTCOUNT('MRR Clients'[user_id]),
   
    FILTER(
    ALLSELECTED('MRR Calendar'[Date]),
    ISONORAFTER('MRR Calendar'[Date], currentdate, DESC)),

    ALLEXCEPT('MRR Clients', 'MRR Clients'[user_id]),
    'MRR Clients'[company_id] = currentcompany,
    'MRR Clients'[user_createdat] <= currentdate,  // User created on or before the selected date
    ('MRR Clients'[user_deletedat] > currentdate || ISBLANK('MRR Clients'[user_deletedat])),  // User deleted after or on the selected date
    'MRR Clients'[company_billing_startdate] <= currentdate
)
rajendraongole1
Super User
Super User

Hi @Mmerinen - if possible please share dummy data for reference. as per the above situation You need to create a measure that calculates the company price divided by the user count, but you need to ensure that the user count is calculated correctly irrespective of the matrix's hierarchy.

so two measure one for user count and another is for company price per user.

 

 

 

 





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

Proud to be a Super User!





Sharing via google sheets:

https://docs.google.com/spreadsheets/d/1DDKKjMESOiNmrfNEn4ytlYRseY0znOFhBHvLLyvZdCQ/edit?usp=sharing

The data table rows are unique by columns: user_id and service_type. Table show all services for all users and therefor also companies.
If a company has a product that has a price on company level (Trio 1 and Plus 2) then the price should be divided by the user count for the matrix visualization.
The user count changes over time, when users get created and deleted. Info is visualized on a matrix, and the hierachy is created though the levels: level1 -> level2 -> level3 (user)

 

I also have a separate calendar MRR Calendar, where the active relationship to MRR Clients table is the column: service_billing_startdate

 

 

Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

September Power BI Update Carousel

Power BI Monthly Update - September 2025

Check out the September 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

Top Solution Authors
Top Kudoed Authors