Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
Solved! Go to 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 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.
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 🙂
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.
Awesome thanks... Works great.
User | Count |
---|---|
89 | |
75 | |
69 | |
65 | |
58 |
User | Count |
---|---|
103 | |
94 | |
74 | |
60 | |
59 |