Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Team, I have a table consisting column header like given below. I am looking for distinct count of vehicle driven less than 10km from last 3 months.
I tried the DAX expression but not getting the correct answer =
Month | Vehicle Number | Km Driven |
Jan 2019 | A12 | 6 |
Dec 2019 | A12 | 6 |
Dec 2019 | A13 | 9.5 |
Jan 2020 | A15 | 11 |
Feb 2020 | A13 | 15 |
Oct 2019 | A11 | 6 |
Nov 2019 | A13 | 9 |
Nov 2019 | A12 | 50 |
Oct 2019 | A11 | 20 |
Jan 2020 | A12 | 15 |
Solved! Go to Solution.
@Anonymous , Try like
countx(filter(summarize(Table, Table[Vehicle Number], "_1",
CALCULATE(sum(Table[Km Driven]),DATESINPERIOD(Master_Data[Month ],MAX(Master_Data[Month ]),-3,MONTH))),[_1]<10),[Vehicle Number])
I am hosting a webinar on 25th April on Power BI, Check Details - https://www.linkedin.com/posts/amitchandak78_webinar-tech-techforgood-activity-6658266754378231808-ye5L
Hi @Anonymous ,
We can create a measure to meet your requirement.
Measure =
VAR selected_month =
DATE ( YEAR ( MAX('Table'[Date]) ), MONTH ( MAX('Table'[Date]) ) - 3, DAY ( MAX('Table'[Date]) ) )
RETURN
CALCULATE (
DISTINCTCOUNT ( 'Table'[Vehicle] ),
'Table'[Date] >= selected_month,
FILTER (
SUMMARIZE (
'Table',
'Table'[Vehicle],
"test", CALCULATE ( SUM ( 'Table'[Km Driven] ) )
),
[test] < 10
)
)
Then we can use a card visual and a line chart visual to show the result.
If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?
BTW, pbix as attached.
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous , Try like
countx(filter(summarize(Table, Table[Vehicle Number], "_1",
CALCULATE(sum(Table[Km Driven]),DATESINPERIOD(Master_Data[Month ],MAX(Master_Data[Month ]),-3,MONTH))),[_1]<10),[Vehicle Number])
I am hosting a webinar on 25th April on Power BI, Check Details - https://www.linkedin.com/posts/amitchandak78_webinar-tech-techforgood-activity-6658266754378231808-ye5L
Hi,
Is it possible if I can aslo calculate the sum of km driven for those vehicles as well? I want to show the sum of km driven in last 3 month just next to the Overall Km driven of all vehicle in another column in table i.e Sum of km
I am trying the below DAX query but unable to fetch the results.
Hi @Anonymous
Can you add a table with the expected result?
Hi All,
Result aree not accurate. Please find the below sample sheet again for your review.
Amit: I have previous year data in sheet as well, So I would like to perfrom the sum from starting and then want to filter last 3 month data. eg. Calculate sum of Km driven and then filter 3 month data and then calculate the distinct count.
Month | Vehicle | Km Driven |
Jan-20 | 1QJ5ES\CC3773\COLORADO | 3 |
Jan-20 | (MMU145DK0LH608973) Holden Colorado | 4 |
Jan-20 | (MMU148DK0LH605575) COLORADO | 5 |
Jan-20 | (MMU147DK0LH623200) Holden Colorado | 2 |
Jan-20 | (MMU147DK0LH623142) Holden Colorado | 2 |
Jan-20 | (1DOC504) 44401 CAT Skid Steer | 5 |
Jan-20 | (156RJQ) 44390 Franna Crane | 4 |
Jan-20 | (1NP3WZ) 73894 Isuzu Tipper - Metro Quarry 1 | 3 |
Jan-20 | 1MP2YD\CC2722\COLORADO | 4 |
Jan-20 | 574YKO\CC2946\COLORADO | 2 |
Jan-20 | CP35SM\CC2497\COLORADO | 2 |
Jan-20 | 1GRK225\CC3263\COLORADO | 3 |
Jan-20 | (MMU148DK0LH605220) Holden Colorado | 3 |
Mar-20 | (156RJQ) 44390 Franna Crane | 3 |
Mar-20 | (1NT9IV) 73848 Hitachi ZX33U Excavator - Geelong | 2 |
Mar-20 | (C86990) CAT Skid Steer 216B 44115/PTQ3890 | 4 |
Mar-20 | (CB63XX) TT178 Hino Dual Cab | 2 |
Mar-20 | 142YPS\CC3128\COLORADO | 3 |
Mar-20 | 1GTR840\90138\TRITON | 3 |
Mar-20 | 1GTD407\90151\TRITON | 3 |
Mar-20 | (1TTX599) Sullair 425H | 3 |
Mar-20 | G70D20FF416D 9/01/2020 | 2 |
Feb-20 | (BI56KQ) SU006Hino Street Sweeper | 2 |
Feb-20 | (C86990) CAT Skid Steer 216B 44115/PTQ3890 | 5 |
Feb-20 | (028SKH) SP030 Mitsubishi Fuso Cement Spreader | 2 |
Feb-20 | XV20JT 73884 Isuzu Beaver Tail - TBA | 4 |
Feb-20 | (1MT3MA) 73862 Isuzu Beaver Tail - At Almighty | 3 |
Feb-20 | 1GLM991\CC2542\COLORADO | 3 |
Feb-20 | LT017 ISUZU NPS SERVICE CREW | 2 |
Need to perfrom sum first to check wether the vehicle has driven less than 10km and then filter data of 3 month less than 10km driven.
Kindly help me.
Hi,
Please check I have alreadt given a table consisting month, Vehicle number and Km driven in month. I want to sum up all the vehicle km driven and then to calculate the distinct count of number of vehicle for last three month.
For example
Month | Vehicle | Km Driven |
Jan 2020 | A13 | 3 |
Dec 2019 | A13 | 4 |
Oct 2019 | A13 | 11 |
Jan 2020 | A12 | 6 |
Dec 2019 | A12 | 3 |
Oct 2019 | A12 | 0.5 |
I am looking for the sum of all vehicle km driven in last 3 months i.e A12 driven 9.5 km and A13 driven 19KM (hence need to exclude it from the data) and the answer of unique vehicle would be A12 only i.e 1
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |