Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Applying Calculated Measure within another Measure

Hi,

 

I previously created two measures (with the help on this community!!). The purpose of the first measure was to find the days between the most recent entry in the table and a fixed date. The second measure was to use the days between to determine the date range between the fixed date and the most recent entry. Both measures worked well for my purpose. This is detailed below:

Days Between = calculate( countrows(date_table), DATESBETWEEN( date_table[Date_field].[Date], date(2020,11,01),
MAX(tblInvestigations[Date Received])))
 
Rolling Measure Before = CALCULATE(COUNTROWS(tblInvestigations),DATESINPERIOD(date_table[Date_field],date(2020,11,01),-[Days Between],DAY))
 
 
I wanted to integrate  [Rolling Measure Before] into another measurement with the aim of filtering for this new  date range. I tried the below and it gave me blanks with no error. Removing the [Rolling Measure Before] measure populated the card indicating the the [Date Recieved] = [Rolling Measure Before] part isn't working properly.

External (Before) =calculate(countrows(tblInvestigations),
FILTER( tblInvestigations, [Date Received] = [Rolling Measure Before] && [Col_1 ] <> BLANK() &&
[Col_1] <> "N/A"))
 
I would appreciate any advice or directions!
 
Cheers

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous ,You are comparing

    [Date Received] = [Rolling Measure Before]

    I doubt Rolling measure Before do not return date.

     

    what you want to achieve here, you can for isblank( [Rolling Measure Before])

     

    or

     

    not isblank( [Rolling Measure Before])

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Amit,
      Did you mean inside the filter like this or outside?

      External (Before) =
      calculate(countrows(tblInvestigations), FILTER( tblInvestigations, NOT(ISBLANK([Rolling Measure Before])) && [Col_1 ] <> BLANK() && [Col_1 ] <> "N/A")))

      This returns blanks and removing the NOT part is the same as removing the [Rolling Measure Before] part.
      • amitchandak's avatar
        amitchandak
        Super User
        5 years ago

        Anonymous , the measure seems correct.

         

        Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Amit, Sorry for the late reply was on break. 

    Your post managed to spark me to try something new which was to take out the measure from the filter and apply it next to the count rows like this:

     

    CALCULATE(COUNTROWS
    (tblInvestigations),DATESINPERIOD(date_table[Date_field],date(2020,11,01),-[Days Between],DAY),
    FILTER(tblInvestigations,[Column_1] = "X"))



    Thanks!