Plot two cumulative distinct count that works with slicers

[jtomczyk]

Hi,

I would like to compare number of all projects to the projects that require service intervention.
I have two queries "Service" and "Orders". Inside each I have those columns
1. Orders  
- ProjectNumber
- CreationDate
- Product
- ProductSoftware
- other

2. Service
- ProjectNumber
- CreationDate
- Reason

I would like to plot cumulative distinct counts of the projects and cumulative distinct count of the projects with service intervention
Something like that

Remarks
- We need to be able to use all slicers (especialy "Product", "ProductSoftware" and "Reason")

- It is necesery that projects with service intervention are distinct, as we can have several intervention for one project but we would like to see only the sum of the problematic projects based on the slicers

[v-henryk-mstf · Accepted solution]

Hi @jtomczyk ,

According to your description, the products that appear in the service table are marked and added up separately, is that what you mean? I think I need to create a separate date table, I did a test reference as follows:

M_1 =
CALCULATE (
    COUNTROWS ( Orders ),
    ALL ( Orders ),
    Orders[CreationDate] <= MIN ( Orders[CreationDate] )
)

M_2 =
CALCULATE (
    COUNTROWS ( Service ),
    ALL ( Service ),
    Service[CreationDate] <= MIN ( Service[CreationDate] )
)

If the problem is still not resolved, please provide detailed error information and test data. Looking forward to your feedback.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@jtomczyk , You need to have common date Table. And one additional copy of project table as diemsion, which will join with project and service.

Date will also join with project and service

Then you can create measure using count from each fact

Cumm project= CALCULATE(Count(project[project]),filter(allselected('Date'),'Date'[date] <=max('Date'[date])))

Cumm service= CALCULATE(Count(service[project]),filter(allselected('Date'),'Date'[date] <=max('Date'[date])))