Activity Date - Filter Date & Logs

[fonsogijon]

I'm trying to create an activity report in Power BI base on the logs in each data set.

My goal is to be able to filter for Date and #Logs. 

For example I would like to see the datasets where:

• There is activity greater than any number selected in the slicer
• Date period selected in the slicer as well

For example: datasets where there is than 10 views in the last month

 

My data it is stored in an "Activity table" and the main columns are "DatasetId" and "CreationTime".

Then, I have the following formula to count the logs:

_Logs = COUNT(Activity[CreationTime])

I have also a calendar table as follow:

dim_Activity_Date = CALENDAR(MIN(Activity[Date]),MAX(Activity[Date]))

At the end, I can get values as this one:

As you can see, using above filters, we can see the logs for the last month, correctly filter by date. However, this dataset shouldn't be there since the number of logs for this dataset in the last month is 4.

I would like to be able to filter dynamically also and say greater than 10, for example.

It needs to be dynamic base on the slicer, as there are some other tables connected to this Activity table, so needs to be filtered by the slicer.

Do you have any idea or suggestion?

Thank you!

[Ritaf1983]

Hi @fonsogijon 

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

[fonsogijon]

https://drive.google.com/file/d/1D7Jli-VMbBzN1a4fxB22xU1r2knFw_ow/view?usp=drive_link 

Hope it works!

Thank you

[v-zhengdxu-msft · Accepted solution]

Hi @fonsogijon 

Unfortunately that I can not access this link:

But here I create a set of sample:

Then add a new table:

Table = GENERATESERIES(0,COUNTROWS('Activity'),1)

Then add a slicer:

And a Measure created:

MEASURE =
VAR _Slicer =
    MIN ( 'Table'[Value] )
VAR _currentID =
    SELECTEDVALUE ( Activity[DatasetId] )
VAR _Count =
    COUNTROWS (
        FILTER ( ALLSELECTED ( 'Activity' ), 'Activity'[DatasetId] = _currentID )
    )
RETURN
    IF ( _Count >= _Slicer, _Count, BLANK () )

The result is as follow:

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.