DAX Help Needed: Asset Numbers that Appear Only Once Under Multiple Filter Conditions

[MSargeant]

Hi All,

I need some help with formulating DAX for use with a stacked column graph by RATING column. The criteria with conditions are as follow.

The count for the number of assets when the following condtions are met:

1. The WORK DESCRIPTION must  =  'Examination' or 'Examination - Revisit'

2. COMPLETED? must = NO

3. STATUS must = COMPLETE

4. If  ASSET NUMBER has a WORK DESCRIPTION 'Examination', COMPLETED? = NO and also has a WORK DESCRIPITION 'Examination - Revisit' with COMPLETED? = YES, it shound be excluded from the count. 

Am looking for results like this:

Below is a data sample:

ASSET NUMBER	STATUS	WORK ORDER	WORK DESCRIPTION	FINISH DATE	SEASON	RATING	COMPLETED?
TT-45009	COMPLETE	341966	Examination	10/10/2023	2023/2024	A	NO
TT-45033	COMPLETE	342039	Examination	10/10/2023	2023/2024	C	NO
TT-45033	COMPLETE	421252	Examination - Revisit	18/02/2024	2023/2024	C	NO
TT-45066	COMPLETE	331060	Examination	10/10/2023	2022/2023	C	NO
TT-45066	COMPLETE	421239	Examination - Revisit	18/02/2024	2023/2024	C	YES
TT-45083	COMPLETE	339234	Examination	10/10/2023	2023/2024	C	NO
TT-45083	DESPATCHED	422362	Examination - Revisit		2023/2024	C
TT-45090	COMPLETE	342042	Examination	10/10/2023	2023/2024	C	NO
TT-45090	COMPLETE	419776	Examination - Revisit	18/02/2024	2023/2024	C	YES
TT-45121	COMPLETE	334760	Examination	10/10/2023	2022/2023	B	NO
TT-45121	DESPATCHED	423643	Examination - Revisit		2023/2024	B
TT-45134	COMPLETE	330935	Examination	10/10/2023	2022/2023	B	NO
TT-45134	DESPATCHED	421403	Examination - Revisit		2023/2024	B
TT-45185	COMPLETE	345518	Examination	10/10/2023	2023/2024	C	NO
TT-45185	DESPATCHED	422354	Examination - Revisit		2023/2024	C
TT-45226	COMPLETE	334685	Examination	10/10/2023	2022/2023	C	NO
TT-45226	DESPATCHED	423653	Examination - Revisit		2023/2024	C
TT-45279	COMPLETE	334695	Examination	10/10/2023	2022/2023	B	NO
TT-45349	COMPLETE	342384	Examination	10/10/2023	2023/2024	C	NO
TT-45349	COMPLETE	419958	Examination - Revisit	18/02/2024	2023/2024	C	YES
TT-45353	COMPLETE	342387	Examination	10/10/2023	2023/2024	A	NO
TT-45353	COMPLETE	419768	Examination	18/02/2024	2023/2024	A	YES
TT-45359	COMPLETE	334691	Examination	10/10/2023	2022/2023	C	NO
TT-45374	COMPLETE	334406	Examination	25/11/2023	2023/2024	C	YES
TT-45374	PLANNING	413089	Evaluation		2023/2024	C
TT-45376	COMPLETE	334403	Examination	25/11/2023	2023/2024	C	YES
TT-45376	PLANNING	413056	Evaluation		2023/2024	C
TT-45422	COMPLETE	334409	Examination	25/11/2023	2023/2024	C	YES
TT-45422	PLANNING	413091	Evaluation		2023/2024	C
TT-45424	COMPLETE	345536	Examination	25/11/2023	2024/2025	B	YES
TT-45426	COMPLETE	334412	Examination	25/11/2023	2023/2024	C	NO
TT-45435	RDESPATCH	345528	Examination		2023/2024	B
TT-45439	COMPLETE	333584	Examination	04/11/2023	2023/2024	D	YES
TT-45442	COMPLETE	340408	Examination	04/11/2023	2024/2025	D	NO
TT-45465	COMPLETE	333591	Examination	04/11/2023	2023/2024	C	YES
TT-45483	COMPLETE	333594	Examination	12/11/2023	2023/2024	C	NO
TT-45485	COMPLETE	333567	Examination	12/11/2023	2023/2024	C	YES
TT-45513	PLANNING	413008	Evaluation		2023/2024	C
TT-45516	COMPLETE	346754	Examination	12/11/2023	2024/2025	B	YES
TT-45647	PLANNING	342550	Examination		2023/2024	A
TT-45863	COMPLETE	318445	Examination	19/11/2023	2023/2024	C	NO

Any assistance would be appreciated.

Thanks

[Anonymous · Accepted solution]

Hi @MSargeant ,

@JamesFR06  said it very well, I have another method here, I hope it will be helpful to you.

1. Create a calculation table and filter out that WORK DESCRIPTION is Examination - Revisit, and COMPLETED? is YES.

FilteredAssets =
FILTER(
SUMMARIZE(
'Table',
'Table'[ASSET NUMBER],
"WorkDesc", MAX('Table'[WORK DESCRIPTION]),
"Completed", MAX('Table'[COMPLETED?])
),
[WorkDesc] = "Examination - Revisit" && [Completed] = "YES"
)

2. Create a measure to count the fields of the compound condition.

AssetCount =
CALCULATE(
DISTINCTCOUNT('Table'[ASSET NUMBER]),
FILTER(
ALL('Table'),
'Table'[SEASON] = MAX('Table'[SEASON])
&& 'Table'[RATING] = MAX('Table'[RATING])
&& 'Table'[WORK DESCRIPTION] IN {"Examination", "Examination - Revisit"}
&& 'Table'[COMPLETED?] = "NO"
&& 'Table'[STATUS] = "COMPLETE"
&& NOT(
'Table'[ASSET NUMBER] IN VALUES(FilteredAssets[ASSET NUMBER])
)
)
)

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

[MSargeant]

Hi @Anonymous,

 This solution works well for my needs.

Thank you for your assistance!

Kind regards,

Michelle Sargeant

[JamesFR06]

HI

I have tried this. Tell me if it is ok for you. I created a new column in your table.

Graph =

if(Assets[STATUS]<>"Complete","No",

    if(and(Assets[STATUS]="COMPLETE",and(Assets[WORK DESCRIPTION]="Examination - Revisit",Assets[COMPLETED]="YES")),"No",

        var nbrows=calculate(COUNTROWS(filter(Assets,Assets[STATUS]="complete"&&Assets[WORK DESCRIPTION]="Examination - Revisit"&&Assets[COMPLETED]="YES")),ALLEXCEPT(Assets,Assets[ASSET NUMBER]))

        return

        if(Assets[STATUS]="complete"&&Assets[WORK DESCRIPTION]="Examination - Revisit"&&Assets[COMPLETED]="NO"&&nbrows>0,"Non","Yes")

    )

    )

[MSargeant]

Hi, thanks but this does not quite work as the items I have hilighted below inside the blue circle should be 'No' for those Asset Numbers because COMPLETED? = YES for for Examination - Revisit  cancels the Examinations where COMPLETED? = NO

Also, the Examinations inside the orange circle where COMPLETED = YES should be 'No 'as the original examination was successfully completed and should not be counted for the graph.