Forum Discussion

fonsogijon's avatar
fonsogijon
Frequent Visitor
2 years ago
Solved

Activity Date - Filter Date & Logs

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!

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

3 Replies

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        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.