Measures Issue

[Malsha]

Hi everyone,
I have created these 3 measures for my power bi report and those 3 measures display as columns in the report..

1.   column Total Worked:

Total Worked =

CALCULATE(

    SUM('v_pbi_ccs_timesheet_data_v1'[Hours Worked]),

    'v_pbi_ccs_timesheet_data_v1'[Timesheet Job Role Name] IN {"Morning Cleaning", "Janitors", "Window Cleaner", "Duty & Morning", "Supervisor"},

    'v_pbi_ccs_timesheet_data_v1'[duration] <> "S",

    'v_pbi_ccs_timesheet_data_v1'[duration] <> "H"

)

2. column Allocated: 

worked hours for selected period =

var start_date = MIN(v_pbi_ccs_timesheet_data_v1[timesheet_date])

var end_date = MAX(v_pbi_ccs_timesheet_data_v1[timesheet_date])

var ans = DATEDIFF(start_date,end_date,DAY) + 1

var hours = SUM(v_pbi_ccs_subunit_v1[allocated_hours])

return

(ans * hours)/7

3. column Difference:

difference = [Total Worked] - [worked hours for selected period]

If total worked column has blank values, I want to show the value as "N/A" in the difference column. How can I do this?

[BITomS]

Hi @Malsha 

For your 3rd measure, I think you can use an IF expression with ISBLANK:

Measure = IF(ISBLANK([Total Worked]),"N/A",[Total Worked] - [worked hours for selected period])

Hope this helps.

[Malsha]

Hi, This works but it gives duplicated records.

[Ritaf1983]

Hi @Malsha 

difference =if(isblank( [Total Worked]),"N/A", [Total Worked] - [worked hours for selected period])

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[Malsha]

Hi, This is working but it gives duplicated records. what's the reason for that.

[Ritaf1983]

Hi @Malsha 
i need to see a pbix to understand it