Search for a command to run...
No comments yet. Be the first to comment.
My previous article on Chat History in Microsoft Agent Framework focused on storing and retrieving chat history using a file-based approach.The intention of that article was to provide a simple introd
Lets take a scenario where you have an MAF (Microsoft Agent Framework) AIAgent that has tool calls to multiple AIFunctions. You definitely wouldn't want an AIAgent to blindly invoke every available to
Imagine a scenario where you have an AI agent that needs to access Azure services, such as Azure Storage. Simply granting the agent open and unrestricted access to Azure resources is not an option. Do
The tittle sounds confusing right ? So how to access Azure services with federated credentials that are tied with User Managed Identities ? Even I was skeptical if it is even possible because its more
My earlier article on securing Azure Storage in ASP.NET core Minimal API's extensively leveraged RBAC access control and through Microsoft Entra ID for user sign-in through OpenID Connect (OIDC) to au
In this short write up we will analyze how to evaluate additional columns along with DAX Time Intelligence functions if used together.
Lets take the following DAX query for example
DEFINE
MEASURE Sales[PY_SalesAmount] =
CALCULATE ( [Sales Amount], PARALLELPERIOD ( ( 'Date'[Date] ), -1, YEAR ) )
EVALUATE
ADDCOLUMNS (
VALUES ( 'Date'[Calendar Year] ),
"PY Sales Amount", [PY_SalesAmount],
"Sales Amount", [Sales Amount]
)
In the query above, there is a defined a measure [PY_SalesAmount] that uses Time intelligence function PARALLELPERIOD to return Sales Amount of the previous year. In other words it shifts the current context of dates to one year back.
Given the interval type and number of intervals, we expect the above query to return the previous year sale amount along with the current year sales amount.
and why should we mark date table as “date” ? and what would be the repercussions if we don’t ?
That is a separate topic for my new detailed upcoming blog.
Lets get back to our DAX query. Execute the earlier query on DAX.do and we have the following output.
So far so good. The DAX query returns Sales Amount from the prior year, through PARALLELPERIOD.
Now in addition to the above output, we might also want a output where we would like to display Brand names with the Year values.
To achieve that ,we can’t just add the Brand column from the Product table because our table expression for ADDCOULUMNS is on the Date table and not the Product table.
Its like adding a column from a Product table in the SQL query that queries a Date table. Something like this
Select ProductName,DateYear from DateTable
Changing our DAX query by replacing our table expression from Date to Product will also not give us Brand wise yearly sales.
DEFINE
MEASURE Sales[PY_SalesAmount] =
CALCULATE ( [Sales Amount], PARALLELPERIOD ( ( 'Date'[Date] ), -1, YEAR ) )
EVALUATE
ADDCOLUMNS (
VALUES ('Product'[Brand]),
"PY Sales Amount", [PY_SalesAmount],
"Sales Amount", [Sales Amount]
)
That is because, both of our measures [PY_SalesAmount] and [Sales Amount] execute under the filter context of the Date and not on Product.
How to fix this ?
We will have to introduce filter context for Date and Product values together and make them available as table expression for ADDCOLUMNS. This can be achieved by using SUMMARIZECOLUMNS function on Brand and Year values as SUMMARIZECOLUMNS always executes under filter context.
DEFINE
MEASURE Sales[PY_SalesAmount] =
CALCULATE ( [Sales Amount], PARALLELPERIOD ( ( 'Date'[Date] ), -1, YEAR ) )
EVALUATE
ADDCOLUMNS (
SUMMARIZECOLUMNS ('Product'[Brand], 'Date'[Calendar Year]),
"PY Sales Amount", [PY_SalesAmount],
"Sales Amount", [Sales Amount]
)
ORDER BY 'Product'[Brand],'Date'[Calendar Year]
For brevity lets add FILTERS in our query to discard out the empty values.
DEFINE
MEASURE Sales[PY_SalesAmount] =
CALCULATE ( [Sales Amount], PARALLELPERIOD ( ( 'Date'[Date] ), -1, YEAR ) )
EVALUATE
FILTER (
ADDCOLUMNS (
SUMMARIZECOLUMNS ( 'Product'[Brand], 'Date'[Calendar Year] ),
"PY Sales Amount", [PY_SalesAmount],
"Sales Amount", [Sales Amount]
),
NOT ISBLANK ( [Sales Amount])
)
ORDER BY
'Product'[Brand],
'Date'[Calendar Year]
That’s it. We have the desired output.
Though Time intelligence functions invokes context transition ,using them with other columns might not get the desired results that you would expect. A decent level of understanding on how DAX functions execute under different contexts is desirable.
Thanks for reading !!!