Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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/
Hi,
You may download my PBI file from here.
Hope this helps.
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/
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
60 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |