cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
abgnfirdaus
Helper I
Helper I

count no. of test

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

DateEquipment no.Result
2/1/2020APriority 1
2/2/2020BPriority 1
3/4/2019APriority 2
4/3/2018ANo 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:

DateEquipment no.ResultCount of test
2/1/2020APriority 13
2/2/2020BPriority 11

 

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

1 ACCEPTED SOLUTION
VahidDM
Super User
Super User

Hi @abgnfirdaus 

 

try this code to add a new table with DAX:

calctables_formulabarempty

 

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:

VahidDM_0-1643079622523.png

 

 

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/

 

 

View solution in original post

2 REPLIES 2
Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
VahidDM
Super User
Super User

Hi @abgnfirdaus 

 

try this code to add a new table with DAX:

calctables_formulabarempty

 

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:

VahidDM_0-1643079622523.png

 

 

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/

 

 

Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors