DAX - To see Group = X and date=today ? I'm Lost

[Benc7777]

Morning all,

Ive been using BI for ages but normally with SQL and I can just write the SQL statement as I like but with DAX im lost.

Basically, Ive found out I can get data directly from our WildIX phone system, I want to get two figures, incoming calls from group "Office" and one Outgoing from group "Office" for today.

How would I go about this, i would like it to always be today and not a date ? 

If anyone can help that would be great, im sorry but im really finding DAX hard i wish it was the same as SQL.......

So im using the below (But with passwords and usernames obs)

let

aud = "cds-ca",
scope = "get",
dateFrom = "2025-01-01",
dateTo = "2025-12-30",

baseAuth = "Basic " & Binary.ToText(Text.ToBinary(user & ":" & pass), BinaryEncoding.Base64),

personalTokenResponse = Web.Contents(pbxUri & "api/v1/personal/token/?service=auth.wildix.com",
[
Headers = [
Authorization=baseAuth,
#"User-Agent"="WildixAuthUtils/1"
]
]
),
personalTokenJson = Json.Document(personalTokenResponse),
personalToken = personalTokenJson[result][token],

authAccessTokenResponse = Web.Contents("https://auth.wildix.com/api/v2/Token/", [Headers=[Authorization="Bearer "& personalToken]]),
authAccessUserTokenJson = Json.Document(authAccessTokenResponse),
authAccessUserToken = authAccessUserTokenJson[result][accessToken],

authOpenIdTokenResponse = Web.Contents("https://auth.wildix.com/api/v2/OpenId/service/",
[Content = Json.FromValue([aud = "cds-ca", scope = "scope"]),
Headers = [
Authorization = "Bearer " & authAccessUserToken,
#"User-Agent"="WildixAuthUtils/1",
#"Content-Type"="application/json"
]
]
),
authOpenIdTokenJson = Json.Document(authOpenIdTokenResponse),
openIdToken = authOpenIdTokenJson[result][id_token],

targetServiceResponse = Web.Contents("https://cds-ca.wildix.com/v1/calls",
[
Headers = [
Accept = "application/json",
Authorization = "Bearer " & openIdToken
],
Query = [
dateFrom = dateFrom,
dateTo = dateTo
]
]
),
targetServiceData = Json.Document(targetServiceResponse),
items = targetServiceData[items],
#"Converted to Table" = Table.FromList(items, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"callCalleeCompany", "callCalleeDevice", "callCalleeEmail", "callCalleeGroupId", "callCalleeGroupName", "callCalleeMos", "callCalleeName", "callCalleePhone", "callCalleeType", "callCalleeUserExtension", "callCalleeUserId", "callCallerCompany", "callCallerDevice", "callCallerEmail", "callCallerGroupId", "callCallerGroupName", "callCallerMos", "callCallerName", "callCallerPhone", "callCallerType", "callCallerUserExtension", "callCallerUserId", "callDestination", "callEndBy", "callEndCause", "callEndCauseStr", "callGroupId", "callGroupName", "callMergeWith", "callRecordings", "callRemoteCountryCode", "callRemoteCountryCodeStr", "callRemoteLocation", "callRemotePhone", "callSplitReason", "callSplitTransferType", "callTrunkDirection", "callTrunkName", "conferenceId", "conferenceSubject", "connectTime", "connectTimeMs", "direction", "duration", "durationMs", "flags", "id", "part", "pbx", "service", "serviceNumber", "sessionId", "startTime", "status", "tags", "talkTime", "talkTimeMs", "time", "type", "waitTime", "waitTimeMs"}, {"callCalleeCompany", "callCalleeDevice", "callCalleeEmail", "callCalleeGroupId", "callCalleeGroupName", "callCalleeMos", "callCalleeName", "callCalleePhone", "callCalleeType", "callCalleeUserExtension", "callCalleeUserId", "callCallerCompany", "callCallerDevice", "callCallerEmail", "callCallerGroupId", "callCallerGroupName", "callCallerMos", "callCallerName", "callCallerPhone", "callCallerType", "callCallerUserExtension", "callCallerUserId", "callDestination", "callEndBy", "callEndCause", "callEndCauseStr", "callGroupId", "callGroupName", "callMergeWith", "callRecordings", "callRemoteCountryCode", "callRemoteCountryCodeStr", "callRemoteLocation", "callRemotePhone", "callSplitReason", "callSplitTransferType", "callTrunkDirection", "callTrunkName", "conferenceId", "conferenceSubject", "connectTime", "connectTimeMs", "direction", "duration", "durationMs", "flags", "id", "part", "pbx", "service", "serviceNumber", "sessionId", "startTime", "status", "tags", "talkTime", "talkTimeMs", "time", "type", "waitTime", "waitTimeMs"})
in
#"Expanded Column1"

[Sahir_Maharaj]

Hello @Benc7777,

Can you please try the following approach:

IncomingCallsToday = 
CALCULATE(
    COUNTROWS('FACT_TABLE'),
    'FACT_TABLE'[callCalleeGroupName] = "Office",
    'FACT_TABLE'[direction] = "Incoming",
    'FACT_TABLE'[startTime] >= TODAY(),
    'FACT_TABLE'[startTime] < TODAY() + 1
)

OutgoingCallsToday = 
CALCULATE(
    COUNTROWS('FACT_TABLE'),
    'FACT_TABLE'[callCallerGroupName] = "Office",
    'FACT_TABLE'[direction] = "Outgoing",
    'FACT_TABLE'[startTime] >= TODAY(),
    'FACT_TABLE'[startTime] < TODAY() + 1
)

[Benc7777]

Hi Sahir,

Many thanks for trying to help I really appriciate it. 

I have added the measure but when i try to add to a visual i get this ?

Is there something I can do or have i done something worng ?

Thanks again

Ben

[Anonymous · Accepted solution]

Thanks for the reply from Sahir_Maharaj , please allow me to provide another insight:

Hi, @Benc7777 
Thanks for reaching out to the Microsoft fabric community forum.

The issue you're encountering is quite common and is usually due to inconsistencies in data types when comparing characters. Please check the data type of your startTime column, which is the date-time column, and ensure it is at least set to "Date".

We recommend debugging in Power Query.

Note that if the data type conversion fails, it might be due to inconsistent format recognition. Typically, the default format is "MM/DD/YYYY" or "YYYY/MM/DD". If your format is "DD/MM/YYYY", you can try selecting other regions, such as French-speaking countries, where the reference format will be displayed.

For more details, please refer to the relevant documentation to ensure consistency in data types and formats.:
Data types in Power BI Desktop - Power BI | Microsoft Learn
We hope these suggestions help resolve your issue. If you have any further questions, feel free to reach out to us at any time.

Best Regards,

Leroy Lu

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