Card Value based on row count

[M_SBS_6]

Hi, 

I have table below. What I'd like to do is add a card and within that, I'd like to count how many dates it took to reach the status of "successful". The table below shows it to have been on the 5th attempt but I'd like to see this value as a card. Any idea if this is possible please? 

Name status. Date

Test1. Nores. 03/01/2024

Test2. Nores. 05/01/2024

Test3. Delivery1 10/01/2024

Test4. Delivery2 12/01/2024

Test5. Successful 14/01/2024

Also, how many days between the earliest date (03/01/2024) and latest date (14/01/2024) again in a card to show 11 days.

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.

[M_SBS_6]

Thanks for your suggestion. My only thought is, what if the first row status was successful? The current logic on the measure would bypass that. 

[Ashish_Mathur]

You are welcome.  What result do you get in that scenario with my measure?  Share the revised dataset and show the expected result.

[Anonymous]

Hi @M_SBS_6 ,

You could try the advice given by @lbendlin  first, which is a good offer. In the meantime, I created an example for you that I hope will help you. You can follow the steps below:

1.Add an index column.

2.Add a new column.

test = 
VAR _1 =
    IF ( 'Table'[status] = "Successful", 'Table'[Name] )
RETURN
    IF (
        'Table'[Date]
            = CALCULATE (
                MIN ( 'Table'[Date] ),
                FILTER ( 'Table', 'Table'[status] = "Successful" )
            ),
        'Table'[Index]
    )

3.Add a new measure.

datediff = 
DATEDIFF (
    CALCULATE ( MIN ( 'Table'[Date] ) ),
    CALCULATE (
        MIN ( 'Table'[Date] ),
        FILTER ( 'Table', 'Table'[status] = "Successful" )
    ),
    DAY
)

Final output:

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

Best Regards,

Ada Wang

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

[M_SBS_6]

Thanks for the help. Unfortunately, the "test" outputs this _ _

I'm not sure the index works as the first record has an index of 36 and the second date has a index of 68 due to other dates and statuses being added in between the first and last date. Apologies, my fault as I didn't explain that very well. 

Name has an ID associated (in my example it's all 125. However, ID 126 was added and the first Status was the 4th of Jan which falls in the middle of the first and second delivery for ID 125. 

[Anonymous]

Hi @M_SBS_6 ,

Based on the information you provided, I understand what you are trying to get at, and it still needs an index column. You can solve the problem by following these steps:
1. Add a new COLUMN.

test = 
VAR _1 = 'Table'[ID]
VAR _2 =
    RANKX ( FILTER ( 'Table', 'Table'[ID] = _1 ), 'Table'[Index],, ASC )
RETURN
    IF ( 'Table'[status] = "Successful", _2 )

2. Add a new MEASURE.

datediff = 
DATEDIFF (
    CALCULATE ( MIN ( 'Table'[Date] ) ),
    CALCULATE (
        MIN ( 'Table'[Date] ),
        FILTER ( 'Table', 'Table'[status] = "Successful" )
    ),
    DAY
)

3. Add the ID into the slicer.

Final output:

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

Best Regards,

Ada Wang

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

[lbendlin]

1. Find the MIN date in your data

2, Find the MIN date in your data where the status is "Successful"

3. On the first card show the number of rows in the table with the date being less or equal to 2

4. on the second card show the difference between 2 and 1