topic DAX - To see Group = X and date=today ? I'm Lost in DAX Commands and Tips

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

Benc7777 — Fri, 24 Jan 2025 08:19:32 GMT

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"

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

Sahir_Maharaj — Thu, 23 Jan 2025 20:21:13 GMT

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 )

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

Benc7777 — Fri, 24 Jan 2025 07:36:39 GMT

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

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

Anonymous — Mon, 27 Jan 2025 05:42:37 GMT

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.