Need help on DAX calculation to get distinct date count

[BalajiS193]

I have a table Name Operator with below Columns 

1. Date

2. Shift

3. Start Time

4. Operator Name

5. Equipment

I need help to calculate following
1. Get Minimum Start Time based on Operator Name and Equipment selection in slicer,
2. From the calculated Minimum Start Time need to Extract the hour from it in format "H AM/MP"(1 AM,2 PM)
3. After Extract the Hour need to get the distict count of date for each derived Hour in above step
4. I need to know how to plot the Derived Hour in X axis on bar chart (which not accepting Measure)

[bhanu_gautam]

To extract hour from calculated minimum starttime in first step not on the actual start time :

First create a measure for MinStartTime

MinStartTime =
CALCULATE(
MIN('Operator'[Start Time]),
ALLEXCEPT('Operator', 'Operator'[Operator Name], 'Operator'[Equipment])
)

Second extract hour

HourAMPM =
FORMAT([MinStartTime], "h AM/PM")

Third get the distinct count of dates

DistinctDateCount =
CALCULATE(
DISTINCTCOUNT('Operator'[Date]),
ALLEXCEPT('Operator', 'Operator'[Operator Name], 'Operator'[Equipment], 'Operator'[HourAMPM])
)

Create a calculated column for Derived hour as Power BI does not accept measure in X-axis

DerivedHour =
FORMAT('Operator'[Start Time], "h AM/PM")

Then use Derivedhour and Distinct datecount in Bar chart

[BalajiS193]

I've tested your solution. However, the HourFormat in calculated column doesn't dynamically update with slicers, and if i create it as  calculated measure can't be used within the ALLEXCEPT function.

HourFormat =
FORMAT(
'Name Operator'[Start Time],
"h AM/PM"
)

Do you have any other option to achive the scenario ?
Operator Name and Equipment is used in slicer so it will keep on change so calculate column is not updating for new calculated measure om slicer change

[BalajiS193]

Thanks for your solution let me check

[BalajiS193]

Any solution for the above request ?

[bhanu_gautam · Accepted solution]

@BalajiS193 , For first one you can create a measure 

MinStartTime =
CALCULATE(
MIN('Name Operator'[Start Time]),
ALLSELECTED('Name Operator'),
VALUES('Name Operator'[Operator Name]),
VALUES('Name Operator'[Equipment])
)

Then you can create a calculated column to extract the hour format create a measure

HourFormat =
FORMAT(
'Name Operator'[Start Time],
"h AM/PM"
)

And for Hour need to get the distict count of date for each derived Hour in above step

DistinctDateCount =
CALCULATE(
DISTINCTCOUNT('Name Operator'[Date]),
ALLEXCEPT('Name Operator', 'Name Operator'[HourFormat])
)

To plot chart use hour format column in X- axis and Distinct date count in Y-axis

[BalajiS193]

Thanks for the reply 
actualy i need to extract hour from calculated minimum starttime in first step not on the actual start time in the table and will the calculated column update base on slicer change ?

Need to achive simlar to below query 

select  

hour(a."st") as hour,

count(distinct b. "Date") as distict_count

from

Operations b,

(

select 

"Date", 

"Shift",  

min(to_timestamp_ntz("Start Time")) as "st"

from Operations 

where "Date" between '2024-01-01' and '2024-06-09'

and "Shift" = 'DS'

and "Operator" = "AAAAA"

group by "Date", "Shift"

) a

where a. "Date" b. "Date" 

and a. "Shift" b. "Shift" 

and b."Shift" = 'DS' 

and "Operator" = "AAAAA"

group by hour(a."st") 

order by hour(a."st")