Forum Discussion

chetanpatel14's avatar
chetanpatel14
New Member
1 year ago
Solved

Summarize the Measure output

In Power Bi, I have a 'public dailyusagereport' with 2 columns called "Name" and "LastLoginDate". My input table as data from Aug 2023 to 6th Nov2024 1.I have provided the date range in the slicer o...
Need Help
  • SamWiseOwl's avatar
    SamWiseOwl
    1 year ago
    It is creating a measure that will use your existing measure on your table 'public dailyusagereport' to work out how many logins each person has and then group them.
     
    Replace LoginTable with the name of your table like so:
     
    Count Logins=
    var logintable = --Create a virtual version of your table, calc for each person their login no
    SUMMARIZE('public dailyusagereport','public dailyusagereport'[Name], "Count login", [LoginCountPerUserMeasure] )

    var currNum = SelectedValue(LoginCount[Value]) --capture the current rows login count

    var final =
    COUNTROWS(FILTER(logintable, [Count login] = currNum)) --Filter the login to only people with that no of logins
    RETURN final
     
    The -- are just comments to explain what the code is doing. You can delete these 🙂
SamWiseOwl's avatar
SamWiseOwl
Super User
1 year ago

Hi chetanpatel14 

Your question is very similar to this one: 
https://community.fabric.microsoft.com/t5/Desktop/Need-to-change-the-category-and-counts/m-p/4287005#M1346153

 

You need to create a data table that contains the number of Logins you want to test for.

If you just want numbers you could use LoginCount = GenerateSeries(1,100,1) that will generate a list of numbers 1 to 100.

 

Then you can use this in your table visual along with a measure:

Count Logins=
var logintable = --Create a virtual version of your table, calc for each person their login no
SUMMARIZE('logintable ','logintable'[Name], "Count login", [LoginCountPerUserMeasure] )

var currNum = SelectedValue(LoginCount[Value]) --capture the current rows login count

var final =
COUNTROWS(FILTER(logintable, [Count login] = currNum)) --Filter the login to only people with that no of logins
RETURN final
chetanpatel14's avatar
chetanpatel14
New Member
1 year ago

Hi,

I didn't get this one
var logintable = --Create a virtual version of your table, calc for each person their login no. How to create this. Can you please provide the steps to achieve the desired output.

 

Thanks again for your response.

 

  • SamWiseOwl's avatar
    SamWiseOwl
    Super User
    1 year ago

     

    If you mean this bit:

    var logintable = --Create a virtual version of your table, calc for each person their login no
    SUMMARIZE('logintable ','logintable'[Name], "Count login", [LoginCountPerUserMeasure] )
     
    The -- is a comment letting you know what the SUMMARIZE function is doing.
    So it is going to your table 'public dailyusagereport' and for each name performing the measure you made. Replace LoginTable with the name of your table.
    • chetanpatel14's avatar
      chetanpatel14
      New Member
      1 year ago

      Hi,

      Thanks for your quick response, I already created the table with the series, What I asked is I didn't understood the below code.

      Count Logins=
      var logintable = --Create a virtual version of your table, calc for each person their login no
      SUMMARIZE('logintable ','logintable'[Name], "Count login", [LoginCountPerUserMeasure] )

      var currNum = SelectedValue(LoginCount[Value]) --capture the current rows login count

      var final =
      COUNTROWS(FILTER(logintable, [Count login] = currNum)) --Filter the login to only people with that no of logins
      RETURN final
      Thanks
      • SamWiseOwl's avatar
        SamWiseOwl
        Super User
        1 year ago
        It is creating a measure that will use your existing measure on your table 'public dailyusagereport' to work out how many logins each person has and then group them.
         
        Replace LoginTable with the name of your table like so:
         
        Count Logins=
        var logintable = --Create a virtual version of your table, calc for each person their login no
        SUMMARIZE('public dailyusagereport','public dailyusagereport'[Name], "Count login", [LoginCountPerUserMeasure] )

        var currNum = SelectedValue(LoginCount[Value]) --capture the current rows login count

        var final =
        COUNTROWS(FILTER(logintable, [Count login] = currNum)) --Filter the login to only people with that no of logins
        RETURN final
         
        The -- are just comments to explain what the code is doing. You can delete these 🙂