Forum Discussion

Giada_Togliatti's avatar
Giada_Togliatti
Icon for Post Patron rankPost Patron
5 years ago
Solved

activity present in more days

Hi, 

I have two table: one is a calendar table and another is a fact table with activities that have a start date and an end date

I need to find for each date in table calendar the number and the list of activities present.

I need to use fields Attività Data_Inizio_Pianificata (start date) and Data_Fine_Pianificata (end date)

Each activity should be counted all the days from Data_Inizio_Pianificata to Data_Fine_Pianificata.

Which formula can I use?

Thank you

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

    Hi, Giada_Togliatti 

    Please check the below picture and the sample pbix file's link down below.

    I tried to create a sample pbix file based on the explanation.

    I suggest not having an active relationship like the below picture.

    If you need to create a measure based on the active relationship, you can always activate it inside the measure by using USERELATIONSHIP function.

    All measures are in the sample pbix file.

     

     

    Activity Count =
    CALCULATE (
    COUNTROWS ( Activities ),
    FILTER (
    Activities,
    Activities[Start] <= MAX ( 'Calendar'[Date] )
    && Activities[End] >= MIN ( 'Calendar'[Date] )
    )
    )
     
     

    Hi, My name is Jihwan Kim.


    If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.


    Linkedin: linkedin.com/in/jihwankim1975/

    Twitter: twitter.com/Jihwan_JHKIM

  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

    Hi, Giada_Togliatti 

    Thank you for your feedback.

    If you have one active relationship, please try to write something like below.

    You can put ALL function inside the measures, to remove filters.

     

    I have created one active relationship in my sample, and please have a look.

     

     

    https://www.dropbox.com/s/dgiy8822ljhqgqr/giada.pbix?dl=0 

     

    Activity Count =
    CALCULATE (
    COUNTROWS ( Activities ),
    FILTER (
    ALL(Activities),
    Activities[Start] <= MAX ( 'Calendar'[Date] )
    && Activities[End] >= MIN ( 'Calendar'[Date] )
    )
    )

5 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    5 years ago

    Hi, Giada_Togliatti 

    Please check the below picture and the sample pbix file's link down below.

    I tried to create a sample pbix file based on the explanation.

    I suggest not having an active relationship like the below picture.

    If you need to create a measure based on the active relationship, you can always activate it inside the measure by using USERELATIONSHIP function.

    All measures are in the sample pbix file.

     

     

    Activity Count =
    CALCULATE (
    COUNTROWS ( Activities ),
    FILTER (
    Activities,
    Activities[Start] <= MAX ( 'Calendar'[Date] )
    && Activities[End] >= MIN ( 'Calendar'[Date] )
    )
    )
     
     

    Hi, My name is Jihwan Kim.


    If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.


    Linkedin: linkedin.com/in/jihwankim1975/

    Twitter: twitter.com/Jihwan_JHKIM

    • Giada_Togliatti's avatar
      Giada_Togliatti
      Icon for Post Patron rankPost Patron
      5 years ago

      Jihwan_Kim , the solution works, but for other visualization I should have that relationship active, do you know how to modify the formula that ignores the relationship for only this measure?

      Thank you

      • Jihwan_Kim's avatar
        Jihwan_Kim
        Icon for Super User rankSuper User
        5 years ago

        Hi, Giada_Togliatti 

        Thank you for your feedback.

        If you have one active relationship, please try to write something like below.

        You can put ALL function inside the measures, to remove filters.

         

        I have created one active relationship in my sample, and please have a look.

         

         

        https://www.dropbox.com/s/dgiy8822ljhqgqr/giada.pbix?dl=0 

         

        Activity Count =
        CALCULATE (
        COUNTROWS ( Activities ),
        FILTER (
        ALL(Activities),
        Activities[Start] <= MAX ( 'Calendar'[Date] )
        && Activities[End] >= MIN ( 'Calendar'[Date] )
        )
        )