Forum Discussion

Mmerinen's avatar
Mmerinen
New Member
2 years ago

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])))

    )
Need Help

3 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    2 years ago

    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.

     

     

     

     

    • Mmerinen's avatar
      Mmerinen
      New Member
      2 years ago

      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

       

       

  • Mmerinen's avatar
    Mmerinen
    New Member
    2 years ago

    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
    )