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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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