topic DAX Help Needed: Asset Numbers that Appear Only Once Under Multiple Filter Conditions in DAX Commands and Tips

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

MSargeant — Mon, 18 Mar 2024 15:37:43 GMT

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

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

JamesFR06 — Mon, 18 Mar 2024 22:38:35 GMT

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")

)

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

Anonymous — Tue, 19 Mar 2024 09:37:44 GMT

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.

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

MSargeant — Tue, 19 Mar 2024 09:39:31 GMT

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.

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

MSargeant — Tue, 19 Mar 2024 11:43:22 GMT

Hi Anonymous,

This solution works well for my needs.

Thank you for your assistance!

Kind regards,

Michelle Sargeant