Need help calculating occurrences using a date slicer and occurrence number

[Anonymous]

Hi 

I have a table  which the picture below show the basic structure. 

PresentationID   CustomerID    Date

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

1                          A                     3/7/2020

2                          B                     7/7/2020

3                          C                     9/8/2020

4                          C                     15/8/2020

5                          B                     29/8/2020

6                          D                     31/8/2020

I need a measure that calculates me the number of customer presentations between a date range and filtered by a number of presentation slicer. Foexample; if date rance is from 3/7/2020 to 31/8/2020 and presentation count slicer set to 1, just patients A and D but presentation count slicer set to  2 , patients B and C should be displayed.  If date rance is from 1/8/2020 to 31/8/2020 and presentation count slicer set to 1, just patient D but presentation count slicer set to  2 , just patient C should be displayed.  

Can anyone help with the measure please? Thanks.

[jdbuchanan71 · Accepted solution]

Hello @ilky

You can create a table to contain the Layout Count selection in this way.

Modeling > New Table:

Presentation Count = 
DATATABLE(
    "Number of Presentations",INTEGER,
    {
        {1},{2},{3},{4},{5},{6},{7},{8},{9},{10}
    }
)

Next, a measure to count the number of preseations.

Presentation Count = COUNTROWS('DataTable')

And finally a measure to apply the selection to the count to simply return the matching rows.

Filtered Presentation Count =
CALCULATE (
    [Presentation Count],
    FILTER (
        VALUES ( 'DataTable'[CustomerID] ),
        [Presentation Count]
            IN ( VALUES ( 'Presentation Count'[Number of Presentations] ) )
    )
)

I've attached my sample file for you to look at.

[jdbuchanan71 · Accepted solution]

Hello @ilky

You can create a table to contain the Layout Count selection in this way.

Modeling > New Table:

Presentation Count = 
DATATABLE(
    "Number of Presentations",INTEGER,
    {
        {1},{2},{3},{4},{5},{6},{7},{8},{9},{10}
    }
)

Next, a measure to count the number of preseations.

Presentation Count = COUNTROWS('DataTable')

And finally a measure to apply the selection to the count to simply return the matching rows.

Filtered Presentation Count =
CALCULATE (
    [Presentation Count],
    FILTER (
        VALUES ( 'DataTable'[CustomerID] ),
        [Presentation Count]
            IN ( VALUES ( 'Presentation Count'[Number of Presentations] ) )
    )
)

I've attached my sample file for you to look at.

[Anonymous]

Hi @jdbuchanan71

I created the Presentation Count using GenerateSeries() function and your answer solved my issue. Many thanks 🙂

Ilky

[Anonymous]

Thank you @jdbuchanan , I cannot open the sample report as it is not compatible with my PBI version but I will try your suggestions in my report.

Regards

Ilky

[amitchandak]

@Anonymous , Hope you had what if a measure to select values 1,2,3 

Then have this measure along with customer in visual

measure =
var _cnt = selectedvalue(Param[count])
return
Countx(filter(summarize(Table,Table[CustomerID], "_1", count(Table[Date])),[_1]>=_cnt),[CustomerID])

https://docs.microsoft.com/en-us/power-bi/desktop-what-if

[Anonymous]

Hi @amitchandak 

Thank you for responding to my query. I am still new to a lot of conceps in Power BI. I have done your suggesttion, however here is the result :

And the measure is :

I don't think this is giving what I am after though. If the No of Presentation is set to 3, I should only see Customers measure = 3 within the date range. The measure shows 1 , why?

thanks again for your help 🙂

Ilky

[amitchandak · Accepted solution]

@Anonymous , you have to remove the arrival date.

or use a measure with

calculate(count(Table[Date]), allexcept(Table, Table[CustomerID]))

try liek

measure =
var _max = selectedvalue(names[first Name])
if(calculate(count(Table[Date]), allexcept(Table, Table[CustomerID])) =_max, 1, blank())

[Anonymous]

Hi @amitchandak , @jdbuchanan71 

Oh ok, thank you so much. 

Is there anyway I can make the MAX value of number of presentations slicer value dynamicly changed based on the date range selected? 

Ilky

[Anonymous]

Hi @amitchandak , @jdbuchanan71 

One more question, I am trying to count the occurence of a customer presentation within the last 7 days and I am getting and error and cannot understand why.

Thanks heaps, you have been wonderful !

Ilky