Grouping counts and distinct counts in DAX

[KMont]

I am trying to figure out a way to get the following table from my data...

I need repeat visitors to our platform.  I created a table that looks like this with a summary table, but can not link it to a time period.  Not sure if I need a DAX measure instead.  The number of accounts are those accounts that have 1 visits to our platform, 2 visits to our platform, 3 visits to our platform,etc during the time period specified in the filter on the page.  I can link the table that this graphic is based on to a network in the model, but can not link it to a date since I am trying to summarize it with counts.  I feel like there should be an easy way to accomplish this that I am missing.  Thanks in advance!

KMont

[v-sgandrathi]

Hi @KMont,

Has your issue been resolved?If the response provided by the community member addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.

If yes, kindly accept the useful reply as a solution and give us Kudos. It would be appreciated.

Thank you for your understanding!

[v-sgandrathi]

Hi @KMont,

We wanted to follow up since we haven't heard back from you regarding our last response. We hope your issue has been resolved.

If my answer resolved your query, please mark it as "Accept Answer" and give Kudos if it was helpful.

If you need any further assistance, feel free to reach out.

Thank you for being a valued member of the Microsoft Fabric Community Forum!

[v-sgandrathi · Accepted solution]

Hi @KMont,

Hi  

We wanted to follow up since we haven't heard back from you regarding our last response. We hope your issue has been resolved.

If my answer resolved your query, please mark it as "Accept Answer" and give Kudos if it was helpful.

If you need any further assistance, feel free to reach out.

Thank you for being a valued member of the Microsoft Fabric Community Forum!

[KMont]

I am so sorry for the delay.  I got side-tracked on other projects.  I tried it out and it worked!!  Thank you so much!  I am struggling a bit to get the table to sort since I am getting a circular reference error on the data table with the groups...1, 2, 3, 4, 5, 6, 7, >=8.  I added a sort column, but it is based on a dax calculation that created the first column.  Any ideas?

[techies]

Hi @KMont i don't know if you were able to download the pbix file, but here is the approach that i followed up

Created disconnected table like this

VisitGroup =

DATATABLE(

    "VisitGroup", STRING,

    {

        {"1"},

        {"2"},

        {"3"},

        {"4"},

        {"5"},

        {"6"},

        {"7"},

        {">=8"}

    }

)

And then the measure as this

AccountsByVisitGroup =

VAR GroupTable =

    ADDCOLUMNS (

        SUMMARIZE ( Visits, Visits[person_id] ),

        "VisitCount", CALCULATE ( COUNTROWS ( Visits ) )

    )

VAR SelectedGroup = SELECTEDVALUE ( VisitGroup[VisitGroup] )

RETURN

    SWITCH (

        TRUE(),

        SelectedGroup = "1", COUNTROWS ( FILTER ( GroupTable, [VisitCount] = 1 ) ),

        SelectedGroup = "2", COUNTROWS ( FILTER ( GroupTable, [VisitCount] = 2 ) ),

        SelectedGroup = "3", COUNTROWS ( FILTER ( GroupTable, [VisitCount] = 3 ) ),

        SelectedGroup = "4", COUNTROWS ( FILTER ( GroupTable, [VisitCount] = 4 ) ),

        SelectedGroup = "5", COUNTROWS ( FILTER ( GroupTable, [VisitCount] = 5 ) ),

        SelectedGroup = "6", COUNTROWS ( FILTER ( GroupTable, [VisitCount] = 6 ) ),

        SelectedGroup = "7", COUNTROWS ( FILTER ( GroupTable, [VisitCount] = 7 ) ),

        SelectedGroup = ">=8", COUNTROWS ( FILTER ( GroupTable, [VisitCount] >= 8 ) )

    )

[techies]

Hi @KMont please find the attached demo pix file here . Hope it is as required

demo-files/community question demo 1.pbix at master · cseprs/demo-files · GitHub

[KMont]

Here is a sample summary table..

person_id	Network Id	Visit Id	Date
a	45	1	8/21/2024 0:00
a	45	2	8/22/2024 0:00
a	45	3	8/23/2024 0:00
a	45	4	8/24/2024 0:00
b	45	5	8/25/2024 0:00
c	45	6	8/26/2024 0:00
d	45	7	8/27/2024 0:00
d	45	8	8/28/2024 0:00
d	45	9	8/29/2024 0:00
d	45	10	8/30/2024 0:00
e	45	11	8/31/2024 0:00
e	45	12	9/1/2024 0:00
f	45	13	9/2/2024 0:00
f	45	14	9/3/2024 0:00
f	45	15	9/4/2024 0:00
f	45	16	9/5/2024 0:00
f	45	17	9/6/2024 0:00

Patient ID could be repeated, encounter id is unique to each row.  Want to count visits and the number of unique patients for each visit group...1 visit, 2 visits, 3 visits, etc.

[KMont]

I have an enounter table that lists all encounters and patient ids and network of each encounter as well as the date of each encounter.  I am trying to group by patient id to see how many encounters each patient has to show in this summary table.  The table needs to filtered by the network and time period selection.  I can provide a sample set if needed.

[techies]

Hi @KMont what does your model include?