filter - Power BI DatesInPeriod() returning blank when there are cases to return - Stack Overflow

admin管理员组

文章数量:1122846

I have a measure that counts the number of incidents in my data.

Incidents = DISTINCTCOUNT([ID])

These fall in various groups depending on where in the process they are so I reference this measure in other instances. E.g., I need to be able to count how many were approved within the previous 6 months. I built a measure that kept throwing a (Blank) error. I needed to filter it to things approved in the previous 6 months, having been assigned (because some things get approval dates that weren’t actually assigned), and I wanted to use the relationship between date approved and my calendar, since the default relationship is on date received.

ApprovedPrev6Mos# = 
    CALCULATE(
    [Incidents], 
    NOT(ISBLANK('baseCombinedData'[DateApproved])), 
    'baseCombinedData'[CASorAssigned] = "Assigned", 
    USERELATIONSHIP('baseCombinedData'[DateApproved], tblCalendar[Date]), 
    DATESINPERIOD(
        'tblCalendar'[Date], 
        EOMONTH(TODAY(), -1), -6, MONTH)
    ) 

Looking at a specific individual I know she had 62 total approved in the data, but only 38 were actually approved in the 6 month period. If I remove the USERELATIONSHIP() call it returns 14 records which is the number received in the period. If leave the USERELATIONSHIP() call, and remove the DATESINPERIOD() it returns the full 62 cases closed. I can’t figure out why using both the DATESINPERIOD() and USERELATIONSHIP() in the same measure is causing a blank return. I’m absolutely certain I’ve done it successfully before. I tested an alternative, to generate a column that simply says yes/no whether a case was approved in the previous 6 months, and if I use it in the following, it returns the correct 38 record count.

TestApprPrev6 = 
    CALCULATE([#victimIncidents]
    , NOT(ISBLANK('baseCombinedData'[DateApproved]))
    , 'baseCombinedData'[CASorAssigned] = "Assigned"
    , USERELATIONSHIP('baseCombinedData'[DateApproved], tblCalendar[Date])
    , 'baseCombinedData'[Approved_prev6] = “Yes”
    )

Can someone explain how I’m using DATESINPERIOD() incorrectly here?

本文标签： filterPower BI DatesInPeriod() returning blank when there are cases to returnStack Overflow