cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper II

## Assistance needed on DistinctCount and Group By

I am needing to do a distinct count of website pages visited per session ID and then count them up at the end to give me a total distinct count of pages viewed per session ID.

I have a table such as this:

User Id, Session Id, Timestamp, URL

A, ExPN, 04/11/2019, HR/Conduct

A, ExPN, 04/11/2019, HR/Diversity

A, ExPN, 04/11/2019, HR/Home

A, ExPN, 04/11/2019, HR/Conduct

A, ExPN, 04/11/2019, HR/Home

A, ExPN, 04/11/2019, HR/Home

A, ExPN, 04/11/2019, HR/Home

Total unique website URLs visited in the above session are 3

A, ceqr, 04/11/2019, HR/Conduct

A, ceqr, 04/11/2019, HR/Home

A, ceqr, 04/11/2019, HR/Turnover

A, ceqr, 04/11/2019, Ops/Home

A, ceqr, 04/11/2019, Ops/Spend

A, ceqr, 04/11/2019, Ops/Drilldown

A, ceqr, 04/11/2019, HR/Conduct

A, ceqr, 04/11/2019, HR/Conduct

Total unique website URLs visited in the above session are 6

What I need from the query is the uniqie website URLs per sessions and then the overall total - so for the above example

Session ExPN has 3 unique pages for this session

Session ceqr has 6 unique pages for this session

Therefore the overall unique page hits across all the sessions should total 9.

However with the following calculated measure:

UniqueVisitsAcrossSessions = CALCULATE(DISTINCOUNTCOUNT('Table Test [url]'),GROUPBY('Table Test','Table Test'[session_id]))

I get an overall UniqueVisitsAcrossSessions =8 but this should be 9

How can I rearrange the measure to show me a total value of 10 as illustrated - really struggling with this and i am new to Power BI any help would be greatly appreciated.  It might be that i need to do something else?

1 ACCEPTED SOLUTION
Resident Rockstar

Well this is a neat trick I picked up this week, I think it was @mwegener who posted it as a response somewhere. The problem is that your expression is evaluated given the filters it operates in. In your case, it evaluates the distinctcount regardless of sessionID in the total row.

Create a measure that creates a temporarily table and sum the column that reflects the distinctcounts like this:

Measure = SUMX(SUMMARIZE(Table4, Table4[Session], "@count", DISTINCTCOUNT(Table4[URL])), [@count])

This results in this table:

This measure is evaluates three times (once per session and also again for the total. However, the context of the first row is the Table4 filtered on Session=ceqr. In the Total row, the context is not filtered on sessionID and thus, the distinctcounts of the temporary table is summed.

Let me know if this works! 🙂

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

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

Proud to be a Super User!

2 REPLIES 2
Resident Rockstar

Well this is a neat trick I picked up this week, I think it was @mwegener who posted it as a response somewhere. The problem is that your expression is evaluated given the filters it operates in. In your case, it evaluates the distinctcount regardless of sessionID in the total row.

Create a measure that creates a temporarily table and sum the column that reflects the distinctcounts like this:

Measure = SUMX(SUMMARIZE(Table4, Table4[Session], "@count", DISTINCTCOUNT(Table4[URL])), [@count])

This results in this table:

This measure is evaluates three times (once per session and also again for the total. However, the context of the first row is the Table4 filtered on Session=ceqr. In the Total row, the context is not filtered on sessionID and thus, the distinctcounts of the temporary table is summed.

Let me know if this works! 🙂

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

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

Proud to be a Super User!

Helper II

@JarroVGIT  many thanks for the solution!! Much appreciated...👍

Announcements

#### Power BI Monthly Update - July 2024

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

#### Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors