Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Mal_Sondh
Helper II
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
JarroVGIT
Resident Rockstar
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:

image.png

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!




View solution in original post

2 REPLIES 2
JarroVGIT
Resident Rockstar
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:

image.png

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!




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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors