cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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/ConductA, ExPN, 04/11/2019, HR/DiversityA, ExPN, 04/11/2019, HR/HomeA, ExPN, 04/11/2019, HR/ConductA, ExPN, 04/11/2019, HR/HomeA, ExPN, 04/11/2019, HR/HomeA, ExPN, 04/11/2019, HR/Home`

Total unique website URLs visited in the above session are 3

`A, ceqr, 04/11/2019, HR/ConductA, ceqr, 04/11/2019, HR/HomeA, ceqr, 04/11/2019, HR/TurnoverA, ceqr, 04/11/2019, Ops/HomeA, ceqr, 04/11/2019, Ops/SpendA, ceqr, 04/11/2019, Ops/DrilldownA, ceqr, 04/11/2019, HR/ConductA, 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! 🙂

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! 🙂

Proud to be a Super User!

Helper II

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

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

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors