Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Count short/long terms???

Hello, 

I need a DAX Formula that counts only rows from 15 Columns, if there are more than 30 rows with (Text) values with no interruption (long term). Thanks in advance !

 

Need Help
Tips and Tricks

7 Replies

  • Samarth_18's avatar
    Samarth_18
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi Anonymous ,

     

    Could you please explain your question with some example or sample data with expected output?

    It would help you get solution for your question more quickly

     

    Thanks,

    Samarth

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      I have a Table with car models. Some cars belong to the company and some are rented. I need to count the number of days for the rented cars, but I have to differentiate long term rented days from short term rented days. Long term= more than 30 days with no interruption, short term= less than 30 days

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

         

  • v-jingzhang's avatar
    v-jingzhang
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Anonymous 

     

    I guess every column represents a car and every row represents a day? Do you want to count days of every rent term for every car?

     

    Best Regards,
    Community Support Team _ Jing

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      I want to count only long rent terms (>30 non-empty rows)

      • v-jingzhang's avatar
        v-jingzhang
        Icon for Community Support rankCommunity Support
        4 years ago

        Hi Anonymous 

         

        The table structure is not friendly for DAX, so I transform it into below structure in Power Query editor. Download the attachment for details. 

         

         

        Then create two calculated columns with the following DAX expressions

        Flag = 
var _value = 'Table'[Value]
var _previousValue = MAXX(FILTER('Table','Table'[Car ID]=EARLIER('Table'[Car ID])&&'Table'[Index]=EARLIER('Table'[Index])-1),'Table'[Value])
return
IF(_value=""||ISBLANK(_value),0,IF(_value=_previousValue,2,1))  // 0->Not rented day, 1->First rented day, 2->Continuous rented day
        Term Day = 
var _firstIndex = IF('Table'[Flag]=1,'Table'[Index],IF('Table'[Flag]=2,MAXX(FILTER('Table','Table'[Car ID]=EARLIER('Table'[Car ID])&&'Table'[Flag]=1&&'Table'[Index]<EARLIER('Table'[Index])),'Table'[Index])))
var _lastIndex = IF('Table'[Flag]<>0,'Table'[Index])
return
IF('Table'[Flag]<>0, _lastIndex - _firstIndex + 1)

         

        Finally create a measure

        Number of Long Terms = COUNTROWS(FILTER('Table','Table'[Term Day]=31))

         

         

        Best Regards,
        Community Support Team _ Jing
        If this post helps, please Accept it as Solution to help other members find it.