topic Re: created and deleted items in one matrix table in DAX Commands and Tips

created and deleted items in one matrix table

cousinitt13 — Fri, 15 Apr 2022 07:51:31 GMT

Hello,

I'm struggling with some, in my opinion, very easy problem.

There is a table with items including creation and delete date

Item	creation date	deletion date
1	April	April
2	May	April
3	April	May
..	...

I'd like to have two measures, where I can select for example "April",
and I get measure "all_created" and "all_closed" for this month .

one my formular see below. the problem here is, it is filtered by creation_date also

TASK (SRQ) - parent Object closed date =

CALCULATE(
DISTINCTCOUNT('ServiceReq'[Number]),
FILTER(ServiceReq,
('ServiceReq'[Closed Date]) >= MIN('Closed Date'[Date]) &&
('ServiceReq'[Closed Date]) <= MAX('Closed Date'[Date])
))

can anybody help in this case?

Re: created and deleted items in one matrix table

cousinitt13 — Fri, 15 Apr 2022 08:28:03 GMT

this is the relation model

Re: created and deleted items in one matrix table

amitchandak — Fri, 15 Apr 2022 08:42:45 GMT

cousinitt13 , If you need close and created on the same period you can work with one date table with Active inactive join and userelationship (to be used in measure)

refer example

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in-power-bi

Re: created and deleted items in one matrix table

cousinitt13 — Fri, 15 Apr 2022 08:49:25 GMT

Hello,
Thanks for the quick answer. I'm understanding this way you sent me, my problem is, I didn't have the possibility to create this datetable cause of restrictions of this dataset.
is there any other way??

If I use the ALL() function, the "closed date" filter seems to be ignored

TASK (SRQ) - parent Object closed date =

CALCULATE(

DISTINCTCOUNT('Task for ServiceReq'[Task Number]),

FILTER(ALL(ServiceReq),

(ServiceReq[Converted To Incident] = FALSE) &&

('ServiceReq'[Closed Date]) >= MIN('Closed Date'[Date]) &&

('ServiceReq'[Closed Date]) <= MAX('Closed Date'[Date])

)

Re: created and deleted items in one matrix table

tamerj1 — Fri, 15 Apr 2022 08:49:31 GMT

Hi cousinitt13
You need only one disconnected date table

TASK (SRQ) - parent Object closed date = CALCULATE ( DISTINCTCOUNT ( 'ServiceReq'[Number] ), 'ServiceReq'[Closed Date] >= MIN ( 'Date'[Date] ), 'ServiceReq'[Closed Date] <= MAX ( 'Date'[Date] ) )

TASK (SRQ) - parent Object open date = CALCULATE ( DISTINCTCOUNT ( 'ServiceReq'[Number] ), 'ServiceReq'[Open Date] >= MIN ( 'Date'[Date] ), 'ServiceReq'[Open Date] <= MAX ( 'Date'[Date] ) )

Re: created and deleted items in one matrix table

cousinitt13 — Fri, 15 Apr 2022 09:01:27 GMT

Not having the opportunity to create relationships and new tables

Re: created and deleted items in one matrix table

tamerj1 — Fri, 15 Apr 2022 09:32:00 GMT

cousinitt13

Alright, I guess in this case also REMOVEFILTERS function is not available. Then you can use one of the date tables in the slicer (I guess better than using two slicers) and use this code

TASK (SRQ) - parent Object open date = CALCULATE ( DISTINCTCOUNT ( 'ServiceReq'[Number] ), ALL ( 'Created Date' ), ALL ( 'Closed Date' ), 'ServiceReq'[Open Date] >= MIN ( 'Closed Date'[Date] ), 'ServiceReq'[Open Date] <= MAX ( 'Closed Date'[Date] ) )

TASK (SRQ) - parent Object Created date = CALCULATE ( DISTINCTCOUNT ( 'ServiceReq'[Number] ), ALL ( 'Created Date' ), ALL ( 'Closed Date' ), 'ServiceReq'[Created Date] >= MIN ( 'Closed Date'[Date] ), 'ServiceReq'[Created Date] <= MAX ( 'Closed Date'[Date] ) )

Re: created and deleted items in one matrix table

v-kkf-msft — Tue, 19 Apr 2022 06:09:57 GMT

Hi cousinitt13 ,

I am not sure if I understood your question correctly. If I use the ALL function, it does not remove the filter on table 'Closed Date'. Could you please share your calculation results?

parent Object closed date = CALCULATE ( DISTINCTCOUNT ( 'ServiceReq'[Number] ), FILTER ( ALL ( ServiceReq ), ( 'ServiceReq'[Closed Date] ) >= MIN ( 'Closed Date'[Date] ) && ( 'ServiceReq'[Closed Date] ) <= MAX ( 'Closed Date'[Date] ) ) )

parent Object created date = CALCULATE ( DISTINCTCOUNT ( 'ServiceReq'[Number] ), FILTER ( ALL ( ServiceReq ), 'ServiceReq'[Created Date] >= MIN ( 'Created Date'[Date] ) && 'ServiceReq'[Created Date] <= MAX ( 'Created Date'[Date] ) ) )

Best Regards,
Winniz