Vehicles with zero utilisation

[KleptoKenny]

I have a register of all assets that is linked to a transaction table which includes number of days on hire if relevant to transaction type. This is linked by the asset number. How do i extract assets that do not have a transaction for sales &/or number of days hired in a month?

[Greg_Deckler]

@KleptoKenny Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[KleptoKenny]

@Greg_Deckler 

Asset Table

EQUIP_CODE	GROUP	DC+GC
NAV21821	STEL	1STEL
NAV21822	STEL	1STEL
NAV21847	FORD	1FORD
NAV21881	FORD	1FORD
NAV21889	FORD	1FORD
NAV21892	FORD	1FORD
NAV21924	IVHE	1IVHE
NAV21933	FORD	1FORD
NAV22085	STEL	1STEL
NAV22090	STEL	1STEL
NAV22094	STEL	1STEL
NAV22236	FORD	1FORD
NAV2648	FORD	1FORD
NAV4122	IVHE	1IVHE
NAV4816	IVHE	1IVHE
NAV4817	IVHE	1IVHE

Transaction table

EQUIPMENT_CODE	Trans Type	HPT_F_TRAN_VAL_BC	HIRE_START_DATE	HIRE_END_DATE	HPT_DAYS
NAV21821	Depre	£183.00			-
NAV21821	Sales	£200.00	01/01/2023	01/02/2023	31.00
NAV21822	Depre	£182.00			-
NAV21822	Sales	£112.50	24/01/2023	27/01/2023	3.00
NAV21847	Depre	£212.00			-
NAV21881	Depre	£267.00			-
NAV21889	Depre	£342.00			-
NAV21889	Sales	£495.00	16/01/2023	25/01/2023	9.00
NAV21892	Depre	£292.00			-
NAV21892	Sales	£330.00	23/01/2023	22/02/2023	31.00
NAV21924	Depre	£875.00			-
NAV21924	Sales	£550.00	16/01/2023	23/01/2023	7.00
NAV21924	Sales	£550.00	09/01/2023	16/01/2023	8.00
NAV21933	Depre	£348.00			-
NAV22085	Depre	£196.00			-
NAV22085	Repair	£202.78			-
NAV22085	Sales	-£64.29	10/01/2023	13/01/2023	-            4.00
NAV22085	Sales	£225.00	06/01/2023	13/01/2023	7.00

On the asset table i have a list of assets that may not have a sales transaction. I would like to be able to apply a filter to select these so i know which assets do not have any sales revenue.

Hope this helps - sorry first time on this forum 🙂

[v-jingzhang · Accepted solution]

Hi @KleptoKenny 

You can create the following measure, then add it to a visual as a filter and make it to show items when value is 0. In the visual, use [EQUIP_CODE] from 'Asset Table'. 

No. of Sales = COUNTROWS(FILTER('Transaction table','Transaction table'[Trans Type]="Sales"))

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[KleptoKenny]

Awesome thanks... Works great.