Countifs in Power BI

[sevens0001]

Hi, everyone! 

I'm trying to convert a countifs formula from an excel report into Power BI. It has two date context filters and I'm having a hard time wrapping my head around the logic and syntax. 

The problem: 

In my data I have job records that have an open date, a cutoff date, and a calculation that counts the difference between the two (days open). I need to be able to count how many jobs were actively open over time as well as the median for the days open metric. 

My model:

- I have a calendar table that controls a universal date slicer 

- I have inactive relationships from both Open Date and Cufoff Date to my my calendar date

My Current Metric: 

Open Reqs by Time = CALCULATE([Open Reqs], <- this is basically a row count

FILTER(Jobs, Jobs[Open Date] <= MAX('Calendar'[Date])), 

FILTER(Jobs, Jobs[Cutoff Date] >= MAX('Calendar'[Date])))

Median Days Open = CALCULATE(MEDIAN(Jobs[Days Open]),

FILTER(Jobs, Jobs[Open Date] <= MAX('Calendar'[Date])),

FILTER(Jobs, Jobs[Cutoff Date] >= MAX('Calendar'[Date])))

I'm modeling this grouped by month/year. To look at this historically, I can only count a job as being open IF its open date is less than the month/year AND it's Cuffoff date is greater than the same month/year. 

I'm not convinced that the metrics that I've written are using the filters like a countifs would in Excel. 

Thanks for any help! 

[Ashish_Mathur]

Hi,

Why do you have 2 inactive relationships - only one should be inactive?  Share some data and show the expected result.  

[sevens0001]

hi, Ashish! 

I have the inactive relationships because I have other metrics that are based off of those dates as well (not as complicated as this), and I want everything tied to one date slicer. Here's some sample data and the desired output. 

The data: 

The Outpout: 

[Ashish_Mathur]

Hi,

Share data in a form that can be pasted in an MS Excel file.

[sevens0001]

Ashish, I'm not sure the best way to go about that, but here's some sample data below. Let me know if this doesn't work. Thanks! 

Job ID	Open Date	Cutoff Date	Days Open
100	1/1/2021		242
105	2/26/2021	5/7/2021	70
107	3/1/2021	4/13/2021	43
126	3/22/2021		162
127	3/22/2021	6/14/2021	84
209	4/7/2021	4/26/2021	19
210	4/7/2021	5/3/2021	26
211	4/7/2021		146
291	5/4/2021	8/26/2021	114
292	5/4/2021		119
293	5/4/2021	8/10/2021	98
294	5/4/2021		119
295	5/5/2021		118
296	5/5/2021	8/24/2021	111
297	5/5/2021	7/1/2021	57
333	5/10/2021	8/2/2021	84
334	5/11/2021		112
335	5/11/2021		112
380	7/2/2021		60
381	7/6/2021		56
497	8/6/2021	8/17/2021	11
498	8/6/2021		25
499	8/7/2021	8/10/2021	3
500	8/9/2021		22
501	8/9/2021		22
502	8/9/2021		22

[Ashish_Mathur]

Hi,

You may download my solution file from here.

Hope this helps.

[sevens0001]

Thank you, Ashish - 

I was able to follow how you transformed the data. Unfortunantly I can't expand my rows by the months that it's open. Too many other metrics rely on distinct agreggations on other fields in 'Jobs' table. It would also degrade the performance. I have over 4000 rows of data already with 5 other sources pulling in. Thank you though! 

[ryan_mayu]

@sevens0001 

you can try this

Open Reqs = CALCULATE(COUNTROWS('Table'),FILTER('Table',('Table'[Open Date]<=max('date'[Date])&&ISBLANK('Table'[cutoff date]))||'Table'[Open Date]<=max('date'[Date])&&'Table'[cutoff date]>max('date'[Date])))

median = CALCULATE(AVERAGE('Table'[Days Open]),FILTER('Table',('Table'[Open Date]<=max('date'[Date])&&ISBLANK('Table'[cutoff date]))||'Table'[Open Date]<=max('date'[Date])&&'Table'[cutoff date]>max('date'[Date])))

I am not sure why the median for july and august is different from your expected output.

pls see the attachment below

[sevens0001]

This actually produced the same result as my original metric! I feel like that is a good sign, but I'm going to wait and hold off on marking it as a solution until I test something else. Thank you! 

[daxer-almighty]

The other one must be adjusted accordingly. Of course, for this to work Calendar must not be connected to Open Date and Cutoff Date.

Open Reqs =
var MinDate = MIN( 'Calendar'[Date] )
var MaxDate = MAX( 'Calendar'[Date] )
var Result =
    CALCULATE(
        COUNTROWS( Jobs ),
        // If the Open Date field is going to be
        // hidden from the user so that they can't
        // slice by it, then you can remove
        // KEEPFILTERS and only leave the condition
        // underneath. Same goes for the other filter.
        KEEPFILTERS( Jobs[Open Date] <= MinDate ),
        KEEPFILTERS( MaxDate <= Jobs[Cutoff Date] )
    )
return
    Result

[sevens0001]

Thanks, Daxer! 

I do need my table connected to the calendar table because there are other metrics being calculated that require me to use "userelationship". Everything in the end should be controlled by a date slicer. 

[daxer-almighty]

To disconnect the table in my measure just use CROSSFILTER with the NONE direction. Easy as 1-2-3.

[sevens0001]

Hi, Daxer! 

I apologize, I'm not sure what you mean? Are you saying to change the crossfilter relationship from my 'Jobs' table to 'calendar' table to "both"?