Forum Discussion

alks_skla_f's avatar
alks_skla_f
Helper II
9 months ago
Solved

Columns in calculate function are not recognized

Hello, I have 2 tables cps_sfdc_cases_history and Timedim. They are connected based on date column in both tables:

I created calculated column in cps_sfdc_cases_history where I am trying to count how many dates between opened date and closed date. Also I exclude weekends:

For some reason the columns are not recognized. I can not use FILTER function, because the performance is so low and PBI can not process the query. How can I fix it, please?

 

Need Help
  • PhilipTreacy's avatar
    PhilipTreacy
    9 months ago

    Hi alks_skla_f 

     

    Download example PBIX file with the below data and DAX

     

    This works with the sample data in my PBIX file - you can adjust it slightly for your Date Table

     

    Weekdays Between = 
VAR StartDate = SELECTEDVALUE(cps_sfdc_cases_history[CaseCreatedDate])
VAR EndDate = SELECTEDVALUE(cps_sfdc_cases_history[First Closed Date])
RETURN
COUNTROWS(
    FILTER(
        'TimeDim',
        'TimeDim'[Date] >= StartDate &&
        'TimeDim'[Date] <= EndDate &&
        'TimeDim'[Weekday] = "Y"   // assuming "Y" marks weekdays
    )
)

     

     

     

    This is an inclusive count i.e. 21 Oct to 23 Oct is 3 days.  Subtract 1 if you just want to count to be 2 days.

     

    In my Date Table I have a column that marks the weekday with "Y" and weekend day with "N" 

     

     

     

    Regards

     

    Phil

     

     

     

     

8 Replies

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    9 months ago

    Hi alks_skla_f 

     

    Download example PBIX file with the below data and DAX

     

    This works with the sample data in my PBIX file - you can adjust it slightly for your Date Table

     

    Weekdays Between = 
VAR StartDate = SELECTEDVALUE(cps_sfdc_cases_history[CaseCreatedDate])
VAR EndDate = SELECTEDVALUE(cps_sfdc_cases_history[First Closed Date])
RETURN
COUNTROWS(
    FILTER(
        'TimeDim',
        'TimeDim'[Date] >= StartDate &&
        'TimeDim'[Date] <= EndDate &&
        'TimeDim'[Weekday] = "Y"   // assuming "Y" marks weekdays
    )
)

     

     

     

    This is an inclusive count i.e. 21 Oct to 23 Oct is 3 days.  Subtract 1 if you just want to count to be 2 days.

     

    In my Date Table I have a column that marks the weekday with "Y" and weekend day with "N" 

     

     

     

    Regards

     

    Phil

     

     

     

     

    • alks_skla_f's avatar
      alks_skla_f
      Helper II
      9 months ago

      I used your code:

      But for some reason nothing is populated in the column:

      SELECTEDVALUE() does not return anything

      How can I fix it?

      • PhilipTreacy's avatar
        PhilipTreacy
        Super User
        9 months ago

        alks_skla_f 

         

        Your DAX is checking for the difference between CaseCreatedDate  and caseHistoryCreatedDate.

         

        Shouldn't it be checking for the difference between CaseCreatedDate  and First Closed Date?

         

        Regards

         

        Phil

  • Nabha-Ahmed's avatar
    Nabha-Ahmed
    Super User
    9 months ago

    Hi 

    Try creating a measure instead of a calculated column to improve performance.
    You can use your Timedim table and filter only the working days:

    WorkingDays =
    VAR OpenDate = cps_sfdc_cases_history[OpenedDate]
    VAR CloseDate = cps_sfdc_cases_history[ClosedDate]
    RETURN
    CALCULATE(
    COUNTROWS(Timedim),
    Timedim[Date] >= OpenDate &&
    Timedim[Date] <= CloseDate &&
    Timedim[IsWeekend] = FALSE()
    )

    This way, Power BI will only count weekdays between your open and close dates, and it will perform much faster than using FILTER inside a calculated column.

    • alks_skla_f's avatar
      alks_skla_f
      Helper II
      9 months ago

      Unfortunately it doesn't work. I assume it wants aggregation function:

       

  • Praful_Potphode's avatar
    Praful_Potphode
    Super User
    9 months ago

    Hi alks_skla_f ,

    try wrapping the column in a function like min,max which will get scalar value.

    calculatedcolumn_test_TimeDiff =
CALCULATE(
    COUNTROWS(TimeDim),
    TimeDim[Date] >= MIN(cps_sfdc_cases_history[CaseCreatedDate]) &&
    TimeDim[Date] <= MAX(cps_sfdc_cases_history[First_Closed_Date]) &&
    TimeDim[Weekday_2] <> 1 &&
    TimeDim[Weekday_2] <> 7
)

     

    Please give kudos or mark it as solution once confirmed.

     

    Thanks and Regards,

    Praful