topic Re: Dax Forumula using power bi in DAX Commands and Tips

Dax Forumula using power bi

EricoVincentciu — Fri, 18 Oct 2024 16:51:40 GMT

i wanna make some calculation like this :

if i filter like this images

count calculation like this

if i filter this one

count calculation like this,

if i filter the dept and type total utilization will be interactive, and i wanna make it real time if it possible

Flat file relation :

real time table i have :

if filtered current date :

active time, down time and not used keep mooving, cause this is real time data

and i wanna ask relation regarding table fuel utillization and reg

Data

i Wanna Make some total utillization like that

so far, i already make it using power bi

this is flat file (not real time)

the yellow mark, already correct but the red one still wrong, i wanna convert it to percentage

SELECT vm.internal_id, vm.seq, vm.start_date_vhl_down AS start_date, vm.finish_date_vhl_down AS finish_date, vm.type AS trx, '' AS shift, vm.start_time_vhl_down AS start_time, vm.finish_time_vhl_down AS finish_time, '' AS duration, CONCAT(COALESCE(vm.start_vhl_down_note, ''), ';', COALESCE(vm.finish_vhl_down_note, '')) AS note, vm.status AS vehicle_status, vm.dept_id, vmd.value, vmd.detail, vr.vehicle_id, vr.type, vr.sub_type, vr.owned, vr.rent, vr.id_type, vt.id_type AS id_type_2 FROM vms_vhl_maintenance AS vm JOIN vms_vhl_mst_dept AS vmd ON vmd.id = vm.dept_id JOIN vms_vhl_reg AS vr ON vr.internal_id = vm.internal_id JOIN vms_vhl_mst_type AS vt ON vt.id = vr.id_type WHERE 1 = 1 --AND vm.internal_id = 'DTOF000004' --AND vm.start_date_vhl_down >= '2024-02-01' --AND (vm.finish_date_vhl_down IS NULL OR vm.finish_date_vhl_down <= '2024-02-29') AND vm.status = 'C';

SELECT vo.internal_id, vo.operational_date AS start_date, vo.operational_date AS finish_date, 'OPERATIONAL' AS trx, '' AS shift, vo.time_start AS start_time, vo.time_finish AS finish_time, '' AS duration, vo.note, vo.status AS vehicle_status, vo.dept_id, vr.vehicle_id, vr.id_type, vr.type, vr.sub_type, vr.owned, vr.rent, vmd.value, vmd.detail, vt.id_type AS id_type_2 FROM vms_vhl_operational AS vo JOIN vms_vhl_mst_dept AS vmd ON vmd.id = vo.dept_id JOIN vms_vhl_reg AS vr ON vr.internal_id = vo.internal_id JOIN vms_vhl_mst_type AS vt ON vt.id = vr.id_type WHERE 1 = 1 --AND vo.internal_id = 'DTOF000004' --AND vo.operational_date BETWEEN '2024-02-01' AND '2024-02-29' AND vo.status = 'C';

--/STEP 2/ (Crossing date ) SELECT vm.internal_id, vm.seq, vm.start_date_vhl_down AS start_date, vm.finish_date_vhl_down AS finish_date, vm.type AS trx, '' AS shift, vm.start_time_vhl_down AS start_time, vm.finish_time_vhl_down AS finish_time, '' AS duration, CONCAT(COALESCE(vm.start_vhl_down_note, ''), ';', COALESCE(vm.finish_vhl_down_note, '')) AS note, vm.status AS vehicle_status, vm.dept_id, vmd.value, vmd.detail, vr.vehicle_id, vr.type, vr.sub_type, vr.owned, vr.rent, vr.id_type, vt.id_type AS id_type_2 FROM vms_vhl_maintenance AS vm JOIN vms_vhl_mst_dept AS vmd ON vmd.id = vm.dept_id JOIN vms_vhl_reg AS vr ON vr.internal_id = vm.internal_id JOIN vms_vhl_mst_type AS vt ON vt.id = vr.id_type WHERE 1 = 1 AND MONTH(vm.start_date_vhl_down) <> MONTH(vm.finish_date_vhl_down) --AND vm.internal_id = 'DTOF000004' --AND ( vm.start_date_vhl_down BETWEEN '2024-02-01' AND '2024-02-29' ) AND vm.status = 'C';

SELECT vm.internal_id, vm.seq, vm.start_date_vhl_down AS start_date, vm.finish_date_vhl_down AS finish_date, vm.type AS trx, '' AS shift, vm.start_time_vhl_down AS start_time, vm.finish_time_vhl_down AS finish_time, '' AS duration, CONCAT(COALESCE(vm.start_vhl_down_note, ''), ';', COALESCE(vm.finish_vhl_down_note, '')) AS note, vm.status AS vehicle_status, vm.dept_id, vmd.value, vmd.detail, vr.vehicle_id, vr.type, vr.sub_type, vr.owned, vr.rent, vr.id_type, vt.id_type AS id_type_2 FROM vms_vhl_maintenance AS vm JOIN vms_vhl_mst_dept AS vmd ON vmd.id = vm.dept_id JOIN vms_vhl_reg AS vr ON vr.internal_id = vm.internal_id JOIN vms_vhl_mst_type AS vt ON vt.id = vr.id_type WHERE 1 = 1 AND MONTH(vm.start_date_vhl_down) <> MONTH(vm.finish_date_vhl_down) --AND vm.internal_id = 'DTOF000004' --AND ( vm.finish_date_vhl_down BETWEEN '2024-02-01' AND '2024-02-29' ) AND vm.status = 'C';

SELECT vm.internal_id, vm.seq, vm.start_date_vhl_down AS start_date, vm.finish_date_vhl_down AS finish_date, vm.type AS trx, '' AS shift, vm.start_time_vhl_down AS start_time, vm.finish_time_vhl_down AS finish_time, '' AS duration, CONCAT(COALESCE(vm.start_vhl_down_note, ''), ';', COALESCE(vm.finish_vhl_down_note, '')) AS note, vm.status AS vehicle_status, vm.dept_id, vmd.value, vmd.detail, vr.vehicle_id, vr.type, vr.sub_type, vr.owned, vr.rent, vr.id_type, vt.id_type AS id_type_2 FROM vms_vhl_maintenance AS vm JOIN vms_vhl_mst_dept AS vmd ON vmd.id = vm.dept_id JOIN vms_vhl_reg AS vr ON vr.internal_id = vm.internal_id JOIN vms_vhl_mst_type AS vt ON vt.id = vr.id_type WHERE 1 = 1 AND MONTH(vm.start_date_vhl_down) <> MONTH(vm.finish_date_vhl_down) --AND vm.internal_id = 'DTOF000004' --AND ( vm.start_date_vhl_down < '2024-02-01' AND vm.finish_date_vhl_down > '2024-02-20' ) AND vm.status = 'C';

can some one help me ??

rajendraongole1 lbendlin Ritaf1983 Kedar_Pande SamWiseOwl parry2k

Re: Dax Forumula using power bi

EricoVincentciu — Sat, 19 Oct 2024 14:54:11 GMT

haii, i wanna make some calculation like this

if i filter departement, date, and type

i got Active Time : 39% Downtime 0 % and not used 61 %

This calculation is interactive based on the filters

another sample is

if i filter departement, date, and type like this

i got this Active Time, Down time and not used

i already make it on power bi

Power bi File

but the result not same like what i make it

this is my excel file : cek and sheet 2

please help me to calculate total Active Time, Down time and not used

as percentages

thank you

@rajendraongole1 @lbendlin @Ritaf1983 @Kedar_Pande @SamWiseOwl @parry2k

Re: Dax Forumula using power bi

samratpbi — Sun, 20 Oct 2024 10:12:26 GMT

Hi, I took the data from sheet 2 and loaded it into PBI.

Then I created 2 simple measuures:

cek value = SUM(cek[Value])

and

cek value % =

DIVIDE(

[cek value],

CALCULATE(

[cek value],

ALLSELECTED(cek[Type])

)

And below is the result:

Hope this helps.

If this helps to resolve your problem, then please mark it as solution provided. Thanks!

Re: Dax Forumula using power bi

EricoVincentciu — Mon, 21 Oct 2024 09:59:11 GMT

thank you for your response, but i wanna ask

Can you help me to swipe this position ??

so i wanna make 100% as vertical and 0.00% as horizontal

and help me make it online (real time base on my sql code)

Data Real Time

thankyou

Re: Dax Forumula using power bi

Anonymous — Fri, 25 Oct 2024 08:44:36 GMT

Thanks for the reply from samratpbi.

Hi EricoVincentciu ,

Please try the following DAX:

Measure = VAR _sum=SUM('Table'[Value]) VAR _total=CALCULATE(SUM('Table'[Value]),ALLEXCEPT('Table','Table'[Type])) RETURN IF(_total<>0,DIVIDE(_sum,_total),0)

Result:

In addition, if you want to achieve real-time results, use Direct Query connection mode to connect to the data source you are querying instead of Import connection mode.

Best Regards,
Zhu

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