dax - how to countrows on close date tickets based on open date filter ?

[oberthou]

Hi,

I need help to create a measure for the following example :

Imagine I have this table (called Tickets).

Number	Opened date	Closed date
T1	01-janv	01-janv
T2	01-janv	02-janv
T3	02-janv	04-janv
T4	03-janv	03-janv

I would like to create a measure [Nb_closed_by_opened_date] that count the number of tickets (countrows) closed at a particular date provided by a pivot table filter based on the Opened date field.

The result I would expect with this measure woule be a pivot table like :

Opened date	Nb_closed_by_opened_date
01-janv	1
02-janv	2
03-janv	0

Can anywone help me ?

Thanks a lot !

[Anonymous]

@oberthou : The problem is to count closed date rows based on filter applied on opened date. 

Measure would look like this : 

Measure_CLosed_Date = CALCULATE(COUNTROWS(Table),ALLEXCEPT(Table,Table[Opened date]))

 

Can you please confirm , if this is what you are looking for. Thanks

[oberthou]

Hi Mridul,

Thanks a lot but it is not what I expect. I want to count the number by closed dates but on the pivot have this based on the the open date filter by row.  Let me better explain. I also made a mistake in my exemple also ... it should look like :

the correct initial Table is actually :

ber	Opened date	Closed date
T1	01-janv	01-janv
T2	01-janv	02-janv
T3	02-janv	02-janv
T4	03-janv	04-janv

I kept your measure in the pivot table below I renamed as Nb Opened.

But the one I need is the other column "Nb_closed_by_open_date"

Nb_opened = CALCULATE(COUNTROWS(Table),ALLEXCEPT(Table,Table[Opened date]))

Nb_closed_by_open_date = ?  that's what I need actually ! ...

Opened date	Nb_opened	Nb_closed_by_opened_date
01-janv	2	1
02-janv	2	2
03-janv	0	0

Indeed, 01-Jan there was only 1 ticket closed (T1) and on 02-Jan there was 2 tickets closed (T2 and T3)

None were closed on 03-Jan.

Hope this is clearer ?

Thanks.

[OwenAuger]

@oberthou

Have a look at this post:

Multiple Relationships Between Tables in DAX

In your situation you probably want an active relationship between Opened Date and your Date table, and an inactive relationship between Closed Date and your Date table.

[Anonymous · Accepted solution]

@OwenAuger : Thanks for sharing the Relationship link. I tried the problem mentioned and it works well. 

@oberthou : Please refer the description and screen shot attached

Number_count_closed_date = CALCULATE(COUNTA(Sheet2[Number]),USERELATIONSHIP(Sheet2[Closed date],Table_1[Opened date]))

Table_1 = SUMMARIZE(Sheet2,Sheet2[Opened date])

Steps are as follows : -

Create a new table with Open Data as mentioned above "Table_1" and a Calculated Column as "Number_count_closed_date".

Create relationship between closed data and open date , it will be (* : 1) relationship, make this active relationship

Create open date and Number_count_closed_date relationship , it will be inactive

create a table from Table_1[Opened date] and Sheet2[Number_count_closed_date]

Go to "Visual level filter" and uncheck blank to remove count of 04th Jan , as this date is NOT in Opened Date Column

Go to "Values" section and check "Show items with no data"

Make sure when you uplaod data , both the column are of Date . I ahve change the format as dd/month/yyyy . you can choose as required

Please see below the screen shot

Hope it helps. Regards 

[oberthou]

Hi mridul, thanks for having taken time to implement the solution for me. Thanks to Owen as well for pointing us in the right direction with the separate date and ruseelationship function.