Number of Days where value x occurs

[ElliotP]

Hi,

I'm trying to work out the number of dates where my number of sessions is greater than or equal to one.

I've tried:

Number of Days With A sessions = CALCULATE(DISTINCTCOUNT('All Web Site Data'[Sessions]), Filter('All Web Site Data', ))

But I can't seem to work out how to Filter so that the Sessions values have to equal to or greater than 1.

I'm working to an end goal of being able to create a visualisation with a horizontal bar chart showing the number of days where there are more than say 1 visitor, 5, 10, 15.

I've taken some inspiration from:

http://community.powerbi.com/t5/Desktop/Hotel-occupancy-rates/td-p/43265/page/2

http://community.powerbi.com/t5/Desktop/Number-of-working-days/m-p/22842#M7236

[ankitpatira]

@ElliotP easy way to do would be to use conditional columns via query editor where Sessions value is equal to greater than 1, then use new created column to get number of days. You can also simply drag and drop Sessions field onto visual, set it as Count and then use filter to filter it where value is greater or equal to 1.

[ElliotP]

It's a good solution; but probably not the best one long term.

I'm looking to be able to catagorise the top 10 for example most occuring number of sessions.

So say there are:

10 dates with 1 session.

15 dates with 10 sessions.

15 dates with 12 sessions

3 dates with 100 sessions.

The number of times 100 sessions happens, then the number of times 12 sessions, ranked.

[v-sihou-msft · Accepted solution]

@ElliotP

To create a visualization with a horizontal bar chart showing the number of days where there are more than say 1 visitor, 5, 10, 15, you can create another table for slicer to select the visitor number. And create a measure using FILTER() to get the results with following steps:

1. Create a “Bucket”table for slicer to select the visitor number.
   
2. Create a measure to get the number of days where there are more than X visitor according to your selection in the slicer.
   

   NumOfDates_NumOfSessions_GreaterOrEqual = 
   IF (
       ISFILTERED ( 'More Than'[More Than Num] ) = FALSE (),
       BLANK (),
       CALCULATE (
           DISTINCTCOUNT('All Web Site Data'[Date]),
           FILTER (
               'All Web Site Data',
               'All Web Site Data'[Sessions] >= MAX ( 'More Than'[More Than Num] )
           )
       )
   )
   

3. Drag a slicer and a Clustered bar chart into your canvas as below.
   
   
   

To categorize the top 10 for example most occurring number of sessions, you can create a rank column and another calculated Top10 table which only shows the top 10 with following steps:

1. Create a measure which calculates the days of each occurring number of sessions.
   

   Occuring_Num_Of_Sessions = 
   CALCULATE (
       DISTINCTCOUNT ( 'All Web Site Data'[Date] ),
       ALLEXCEPT ( 'All Web Site Data', 'All Web Site Data'[Sessions] )
   )
   

2. Create a column which store the ranks of each occurring number of sessions.
   

   Rank_Column = 
   RANKX ( ALL ( 'All Web Site Data' ), [Occuring_Num_Of_Sessions],,, DENSE )
   

3. Create a calculated table which only shows the top 10.
   

   Top10Table = 
   CALCULATETABLE ( 'All Web Site Data', 'All Web Site Data'[Rank_Column] <= 10 )
   

4. Drag table chart into your canvas.
   
5. If you want all the ranks, you can only create a measure.
   

[ElliotP]

Evening,

Sorry for not responding sooner, I've been busy.

Thank you so much, I really appreciate it.