Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
A_Wizard
Regular Visitor

Cumulative count split by attribute's values

Hello community,

I have been trying to find a solution to the following problem. This is the scenario:

ProjektReqIDStatusModifiedDate
C12345AGREED02.07.2022 00:00:00
C112233PARTLY_AGREED03.07.2022 00:00:00
C112233NOT_AGREED04.07.2022 00:00:00
C112233AGREED05.07.2022 00:00:00
C9876AGREED06.07.2022 00:00:00

This table contains 3 requirements, one of which is being changed 3 times on consecutive days. I am now trying to calculate the number of ReqIDs cumulated per day, and split by state, using this measure:

# of ReqID = CALCULATE( DISTINCTCOUNTNOBLANK('Tabelle1'[ReqID]), FILTER ( ALLSELECTED('Tabelle1'), 'Tabelle1'[ModifiedDate] <= MAX ('Tabelle1'[ModifiedDate]) ))

 

This would be my expected result:

 total of ReqIDAGREEDPARTLY_AGREEDNOT_AGREED
02. Jul11  
03. Jul211 
04. Jul21 1
05. Jul22  
06. Jul33  

 

Unfortunately, this is the result:

A_Wizard_0-1664892580861.png

Why am I getting a count of 2 for 3rd and 4th for the states? How can I avoid this?

Thanks a lot for your help!

Regards,

Marc

6 REPLIES 6
Vera_33
Resident Rockstar
Resident Rockstar

Hi @A_Wizard 

 

Should be the ALLSELECTED, you can do like this

Vera_33_0-1664940029058.png

# of ReqID = 
CALCULATE(COUNTROWS(VALUES(Tabelle1[ReqID])), Tabelle1[ModifiedDate]<=MAX(Tabelle1[ModifiedDate]))

 

Thanks a lot for your answer, Vera.

There is still something missing though: now the sums in lines don't add up. For 3rd and 4th, there should be a 1 for AGREED, too.

(Sorry, I can't get the formatting nice on my "expected results" table.)

Hi @A_Wizard 

 

If the AGREED should have 1 as cumulative, then why the other two do not have? The total still doesn't add up...I had a another date table, did not use the date column in the same table.

 

You need to explain your logic

Vera_33_0-1664965391818.png

 

 

 

Thanks again for looking into my problem, Vera!

So, I got 3 requirements in total, but not from the first day onwards.

On 2nd, there is only 1.

On 3rd, another one is added (112233). Total count is 2. But 1 requirement is AGREED, the other new one is PARTLY_AGREED.

On 4th, the new one (112233) changes its state to NOT_AGREED. Total count is still 2, but now there is still the one from the beginning (AGREED) and now one in NOT_AGREED. So, a "1" is expected to appear twice.

On 5th, the requirement 112233 is finally agreed, total count is still 2 and therefore all 2 are AGREED.

On 6th another new requirement is added, therefore the total count is 3, and they are all AGREED (=3 in that column). 

 

Hi @A_Wizard 

 

I am not sure how many scenarios you might have, say NOT_AGREED and PARTLY_AGREED will only appear once for each requirement? you need to go through all scenarios...here is one based on your sample only

Vera_33_0-1665016616415.png

# of ReqID = 
VAR CurDate=MAX(Dates[Date])
RETURN
IF(SELECTEDVALUE('Tabelle1'[Status])="NOT_AGREED"||SELECTEDVALUE('Tabelle1'[Status])="PARTLY_AGREED",
CALCULATE(COUNTROWS(VALUES(Tabelle1[ReqID])),Tabelle1[ModifiedDate]=CurDate),
CALCULATE(COUNTROWS(VALUES(Tabelle1[ReqID])),Tabelle1[ModifiedDate]<=CurDate))

 

Hi Vera,

thanks again for your explanation. Unfortunately I'd like to have this a little bit more dynamic and do not want to explicitly describe every scenario, as you call it.

Can you explain, why my initial approach does not work? Your last solution is only a workaround to make my example work, but of course it would not work with my actual data, because I always need the cumulated data until the date of a line, and not only from that specific date.

So, why is my initial expression returning 2 for PARTLY_AGREED and NOT_AGREED on 3rd and 4th?

Regards,

Marc

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.