Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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?
Solved! Go to Solution.
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!
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!
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
21 | |
15 | |
14 | |
11 | |
9 |
User | Count |
---|---|
27 | |
23 | |
12 | |
11 | |
10 |