count no. of test

[abgnfirdaus]

Hello there, I need some help here. Based on the table below:

Date	Equipment no.	Result
2/1/2020	A	Priority 1
2/2/2020	B	Priority 1
3/4/2019	A	Priority 2
4/3/2018	A	No action needed

I want to create a column that can count number of test per equipment and only show latest date of equipment. The table should be like this:

Date	Equipment no.	Result	Count of test
2/1/2020	A	Priority 1	3
2/2/2020	B	Priority 1	1

Hope you guys help me by showing some tutorial. thank you very much 

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[VahidDM · Accepted solution]

Hi @abgnfirdaus 

try this code to add a new table with DAX:

New Table = 
ADDCOLUMNS (
    VALUES ( 'Table'[Equipment no.] ),
    "Date",
        CALCULATE (
            MAX ( 'Table'[Date] ),
            ALLEXCEPT ( 'Table', 'Table'[Equipment no.] )
        ),
    "Result",
        CALCULATE (
            MAX ( 'Table'[Result] ),
            FILTER (
                'Table',
                'Table'[Equipment no.] = EARLIER ( [Equipment no.] )
                    && 'Table'[Date]
                        = CALCULATE (
                            MAX ( 'Table'[Date] ),
                            ALLEXCEPT ( 'Table', 'Table'[Equipment no.] )
                        )
            )
        ),
    "Count of Test",
        CALCULATE (
            COUNTROWS ( 'Table' ),
            FILTER ( 'Table', 'Table'[Equipment no.] = EARLIER ( [Equipment no.] ) )
        )
)

Output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/