Power BI: Parameter Table and Dynamic Date Range

As we know Power BI is all about filter context, which brings us quite a lot convenience. But for some cases, it might not be what we want. For instance, what if I want to show amount of sales for last N months by a calendar month slicer? Because the slicer puts a constraint on the calendar month, you won't be able to show sales amount month by month. As illustrated below, how can we see total sales for 2012-12, 2012-11, etc.?

Normally what we can do is creating a disconnected date dimension, and using this disconnected date dimension for the slicer. But sometimes it could be very confusing to end users. So instead of creating a second date dimension, we can try to use date member from the fact table directly. So let's start today's topic:

Firstly the demonstration I built is from AdventureWorksDW2014.

To make it simple, I only loaded DimDate and FactInternetSales tables. Then, I removed default relationships and create the date relationship between FullDateAlternateKey and OrderDate columns directly.

Finally I created a custom column in the DimDate table for calendar month:

Calendar Month = FORMAT(DimDate[FullDateAlternateKey], "yyyy-MM")

Then we need a parameter table, which gives us control of how many months of sales user wants to see. In the real world scenario, we may need to create a junk dimension to hold these values. But in this demonstration, I just manually input the data.

Now let's create two simple measures:

Total Sales = SUM(FactInternetSales[SalesAmount])

Selected Period = IF(HASONEVALUE('Parameter Table'[Parameter Value]), VALUES('Parameter Table'[Parameter Value]))


If we follow normal steps to create visuals (create a slicer from 'DimDate'[Calendar Month], create a slicer from 'Parameter Table'[Parameter Name], then drop the table visual for calendar month and total sales), you will find the table visual won't show last N months sales, because the calendar month slicer puts a filter context to the fact table.

So let's make three changes to make it work:

1. add a calculate order month column into the fact table

Order Month = FORMAT(FactInternetSales[OrderDate], "yyyy-MM" )

2. add a measure for total sales of last N months. (note: Max function gives us the last date within the current filter context - the calendar month slicer value in this case)

Sales (in period) = CALCULATE([Total Sales], DATESINPERIOD(DimDate[FullDateAlternateKey], MAX(DimDate[FullDateAlternateKey]), -[Selected Period], MONTH))

3. now instead of using 'DimDate'[Calendar Month] and [Total Sales] for the table, we drag and drop 'FactInternetSales'[Order Month] and [Sales (in period)] into the table visual.

Easy job, isn't it? :D

Pull Exchange email attachment via Exchange Service Binding

To be honest, I had never thought there would be a need to pull attachment from on-premise Exchange Server via Exchange Service Binding, in the year of 2018. But I was wrong :(

Basically we have two ways to read email from on-premise Exchange. The first option is utilizing Exchange Web Service managed API. It can be used for both on-premise Exchange server and O365 (an example I blogged before). The other option is today's topic: Exchange Service Binding, which by any means, should be avoid! Now let's have a look the basic routine:

• find the root of the email account;
• locate the base email folder (normally it should be the inbox folder);
• pull emails from the base folder;
• get email attachments' id from the email item;
• pull attachments from email by attachment item id;

To locate the root folder of the account (NOT the inbox folder)

        public static string GetRootFolder(ExchangeServiceBinding esb)
        {
            DistinguishedFolderIdType[] dfit = new DistinguishedFolderIdType[1];

            dfit[0] = new DistinguishedFolderIdType();
            dfit[0].Id = DistinguishedFolderIdNameType.root;

            FolderResponseShapeType frst = new FolderResponseShapeType();
            frst.BaseShape = DefaultShapeNamesType.AllProperties;

            GetFolderType gftRoot = new GetFolderType();
            gftRoot.FolderIds = dfit;

            gftRoot.FolderShape = frst;
            GetFolderResponseType gfrt = esb.GetFolder(gftRoot);
            FolderInfoResponseMessageType firmt = ((FolderInfoResponseMessageType)gfrt.ResponseMessages.Items[0]);

            if (firmt.ResponseClass == ResponseClassType.Success)
                return ((FolderInfoResponseMessageType)gfrt.ResponseMessages.Items[0]).Folders[0].FolderId.Id;
            else
                return null;

        }

After we have the root, we can navigate to the specific folder by folder name, inbox, archive, deleted folder, whatever you like :)

        public static BaseFolderType GetFolder(ExchangeServiceBinding esb, string parentFolderId, string folderName)
        {
            if (esb == null || string.IsNullOrEmpty(parentFolderId))
                return null;

            FolderIdType[] fit = new FolderIdType[1];
            fit[0] = new FolderIdType();
            fit[0].Id = parentFolderId;

            FolderResponseShapeType frst = new FolderResponseShapeType();
            frst.BaseShape = DefaultShapeNamesType.AllProperties;

            PathToUnindexedFieldType ftFolderName = new PathToUnindexedFieldType();
            ftFolderName.FieldURI = UnindexedFieldURIType.folderDisplayName;

            ConstantValueType cvt = new ConstantValueType();
            cvt.Value = folderName;

            FieldURIOrConstantType ctFolderName = new FieldURIOrConstantType();
            ctFolderName.Item = cvt;

            ContainsExpressionType cet = new ContainsExpressionType();
            cet.Constant = cvt;
            cet.Item = ftFolderName;
            cet.ContainmentComparison = ContainmentComparisonType.IgnoreCase;
            cet.ContainmentComparisonSpecified = true;
            cet.ContainmentMode = ContainmentModeType.FullString;
            cet.ContainmentModeSpecified = true;
            RestrictionType rt = new RestrictionType();
            rt.Item = cet;
             
            FindFolderType fft = new FindFolderType();
            fft.Traversal = FolderQueryTraversalType.Deep;
            fft.ParentFolderIds = fit;
            fft.FolderShape = frst;
            fft.Restriction = rt;

            FindFolderResponseType ffrt = esb.FindFolder(fft);

            ResponseMessageType rmt = ((ResponseMessageType)ffrt.ResponseMessages.Items[0]);

            if (rmt.ResponseClass == ResponseClassType.Success)
            {
                BaseFolderType[] bfts = ((FindFolderResponseMessageType)ffrt.ResponseMessages.Items[0]).RootFolder.Folders;
                if (bfts.GetLength(0) > 0)
                    return bfts[0];
                else
                    return null;
            }
            else
                return null;
        }

Now we can get all email items from the folder we located

        public static List GetEmailItems(ExchangeServiceBinding esb)
        {
            List lstItems = new List();

            FindItemType fitRequest = new FindItemType();
            fitRequest.Traversal = ItemQueryTraversalType.Shallow;
            ItemResponseShapeType irst = new ItemResponseShapeType();
            irst.BaseShape = DefaultShapeNamesType.AllProperties;
            fitRequest.ItemShape = irst;

            DistinguishedFolderIdType[] dfits = new DistinguishedFolderIdType[1];
            dfits[0] = new DistinguishedFolderIdType();
            dfits[0].Id = DistinguishedFolderIdNameType.inbox;
            fitRequest.ParentFolderIds = dfits;

            FindItemResponseType fitResponse = esb.FindItem(fitRequest);
            FindItemResponseMessageType findItemResponseMessageType = (FindItemResponseMessageType)fitResponse.ResponseMessages.Items[0];

            var responseContents = findItemResponseMessageType.RootFolder.Item;
            var items = ((ArrayOfRealItemsType)responseContents).Items;

            if (items != null && items.Length > 0)
            {
                foreach (var item in items)
                {
                    lstItems.Add(item);
                }
            }

            return lstItems;
        }

Now here is a very important concept: if you google Exchange Service Binding, you will find the concept of the attachment in ESB is a bid confusing: the Attachments property returned from email ItemType is not the actual attachment itself. So we need to get the item id of the attachment, then we can get the actual attachment from the item id. Below is the code to construct a dictionary object for attachment id and attachment name:

        public static Dictionary GetAttachments(ExchangeServiceBinding esb, ItemIdType itemId)
        {
            Dictionary attachments = new Dictionary();

            GetItemType gitRequest = new GetItemType();
            gitRequest.ItemIds = new ItemIdType[] { itemId };
            gitRequest.ItemShape = new ItemResponseShapeType();
            gitRequest.ItemShape.BaseShape = DefaultShapeNamesType.AllProperties;

            PathToUnindexedFieldType hasAttachPath = new PathToUnindexedFieldType();
            hasAttachPath.FieldURI = UnindexedFieldURIType.itemHasAttachments;

            PathToUnindexedFieldType attachmentsPath = new PathToUnindexedFieldType();
            attachmentsPath.FieldURI = UnindexedFieldURIType.itemAttachments;

            gitRequest.ItemShape.AdditionalProperties = new BasePathToElementType[] { hasAttachPath, attachmentsPath };

            GetItemResponseType giResponse = esb.GetItem(gitRequest);
            ItemInfoResponseMessageType iirm = (ItemInfoResponseMessageType)giResponse.ResponseMessages.Items[0];

            if (iirm.ResponseCode == ResponseCodeType.NoError)
            {
                ItemType it = iirm.Items.Items[0];

                if (it.HasAttachments && it.Attachments != null && it.Attachments.Length > 0)
                {
                    foreach (AttachmentType attachmentItem in it.Attachments)
                    {
                        FileAttachmentType fat = (FileAttachmentType)attachmentItem;
                        if (fat != null)
                        {
                            attachments.Add(fat.AttachmentId.Id, fat.Name);
                        }
                    }

                    
                }
            }

            return attachments;
        }

Then we can get the attachment and save to the disk

        public static void GetAttachmentsOnItem(ExchangeServiceBinding esb, ItemIdType itemId, string destPath)
        {
            GetItemType gitRequest = new GetItemType();
            gitRequest.ItemIds = new ItemIdType[] { itemId };
            gitRequest.ItemShape = new ItemResponseShapeType();

            gitRequest.ItemShape.BaseShape = DefaultShapeNamesType.IdOnly;

            PathToUnindexedFieldType hasAttachPath = new PathToUnindexedFieldType();
            hasAttachPath.FieldURI = UnindexedFieldURIType.itemHasAttachments;

            PathToUnindexedFieldType attachmentsPath = new PathToUnindexedFieldType();
            attachmentsPath.FieldURI = UnindexedFieldURIType.itemAttachments;
            gitRequest.ItemShape.AdditionalProperties = new BasePathToElementType[] { hasAttachPath, attachmentsPath };

            GetItemResponseType getItemResponse = esb.GetItem(gitRequest);

            ItemInfoResponseMessageType getItemResponseMessage = (ItemInfoResponseMessageType)getItemResponse.ResponseMessages.Items[0];

            if (getItemResponseMessage.ResponseCode == ResponseCodeType.NoError)
            {
                ItemType item = getItemResponseMessage.Items.Items[0];

                if (item.HasAttachments && (item.Attachments != null) && (item.Attachments.Length > 0))
                {
                    List lstRait = new List();

                    for (int attachmentIndex = 0; attachmentIndex < item.Attachments.Length; attachmentIndex++)
                    {
                        FileAttachmentType fat = (FileAttachmentType)item.Attachments[attachmentIndex];
                        if (fat != null)
                        {
                            RequestAttachmentIdType rait = new RequestAttachmentIdType();
                            rait.Id = fat.AttachmentId.Id;
                            lstRait.Add(rait);
                        }
                    }

                    GetAttachmentType gat = new GetAttachmentType();
                    gat.AttachmentShape = new AttachmentResponseShapeType();
                    gat.AttachmentIds = lstRait.ToArray();

                    GetAttachmentResponseType getAttachmentResponse = esb.GetAttachment(gat);

                    foreach (AttachmentInfoResponseMessageType attachmentResponseMessage in getAttachmentResponse.ResponseMessages.Items)
                    {
                        if (attachmentResponseMessage.ResponseCode == ResponseCodeType.NoError)
                        {
                            FileAttachmentType fileAttachment = attachmentResponseMessage.Attachments[0] as FileAttachmentType;
                                                    
                            using (FileStream file = File.Create(Path.Combine(destPath, fileAttachment.Name)))
                            {

                                file.Write(fileAttachment.Content, 0, fileAttachment.Content.Length);
                                file.Flush();
                                file.Close();
                            }

                        }

                    }

                }

            }

        }