Author: Ash

  • USERNAME() to extract the name of the logged-in user in Power BI – Part 2

    USERNAME() to extract the name of the logged-in user in Power BI – Part 2

    USERNAME() and USERPRINCIPALNAME() can be used in various ways. However, In this post, we will look at two Power BI functions, USERNAME() and USERPRINCIPALNAME(). To extract either the first name or the user’s last name, they differ when using Power BI Desktop and Power BI Service.

    We will pick up where we left off in part 1 of this post. As promised, here is the explanation in detail.

    Username
    MyUsername =
VAR myusername =
    USERNAME ()
VAR findmyatrate = "@"
VAR findmyslash = "\"
VAR totallen =
    LEN ( USERNAME () )
VAR myname =
    IF (
        FIND ( findmyatrate, myusername, 1, 0 ) = 0,
        RIGHT ( myusername, totallen - FIND ( findmyslash, myusername, 1 ) ),
        LEFT ( myusername, FIND ( findmyatrate, myusername, 1 ) - 1 )
    )
VAR capfirstchar =
    UPPER ( LEFT ( myname, 1 ) )
VAR addremchar =
    LOWER ( RIGHT ( myname, LEN ( myname ) - 1 ) )
VAR comboth = capfirstchar & addremchar
RETURN
    "Welcome : " & comboth

    The behavior of these functions varies. In Power BI Desktop, it shows the local machine name\user name, for example, domain\username. You can find yours easily by going to cmd and typing in whoami. If I was to use \ as a means to find the username, then in Power BI Service, I will have a problem. This is because, in the Power BI Service, it shows username@domain.com. To accommodate for this, we need to search(find) for the index of @.

    Once we know this, the rest of the work is as easy as finding the index value of either @ or \ and counting the correct number of characters from it. But wait, there is more.

    Though we know that the behavior is different in Desktop and Service, we also need to ensure that we use both right and left functions. this is because the user’s name itself will shift the position. As you can see in the example above when in Desktop, the username is on the right (meaning after the \), and in Service, the username is on the left (meaning before the @)

    Once this is done, it is just a matter of checking from which starting position the Find function returns a 0. Wrap this into an IF statement, and it will do the trick. At this point, all the heavy lifting is done.

    To capitalize the first character, once again use left but wrap it around the upper function. This will change the case sensitivity to the upper case. Do the same for the rest of the characters and make when lower.

    Finally, return the function and add a “welcome” or any other greeting if you so desire it, and now you can greet your users who logged in with a nice welcome message.

    Though, I enjoyed working on this. There are a few things that I realized, and you will have to adjust your logic accordingly. The biggest adjustment will need to be made when you do have firstname.lastname@domain.com. Here you will need to extract the first name based on the first occurring. (period) and then do the rest. Overall the same logical reasoning should work just fine.

    I hope you liked this. Let me know what you think or if you have a different approach to this.

  • Create a histogram in Power BI – Method 1

    Create a histogram in Power BI – Method 1

    Hello all,

    In this series of blog posts, we will look into 2 different ways to create a histogram (bins) in Power BI

    There is no out of the box visualization that currently exists for a histogram. In this post, we will see one way to create your won histograms or bins (another name for it). Let us first talk about the data that we will be using for this post. The sample data can be downloaded here.

    sample_DataDownload

    Our sample data contains columns such as employee ID, sale amount, hours, and name of each employee.
    The task that we need to accomplish is to bin the count employees by their efficiency. In other words, we need to find how many employees have X efficiency and how many employees have Y efficiencies so on and so forth.

    We will be taking two approaches to achieve this. Method 1 will create a calculated column in the data table and we will use the SWITCH() function to bin the employees.
    Method 2 will make use of the disconnected table and using the VALUES() DAX function we will find the corresponding employee.


    Let us begin with method 1.

    Click on get data and select the sample file.

    1. At this point, there is no need to transform the data as our data for this exercise is already clean. Once the data is loaded, click on the data/model tab and right-click on the table name and let us first rename it to Main_Data.

    First of all, let us create the following measures. These are Total Sales, Total Hours, and Efficiency.

    • Total Sales is the sum of all the sale amount.
    • Total Hours is the sum of all the hours.
    • Efficiency is Total Sales divided by Total Hours. For Efficiency measure, we will use the DIVIDE() function.  
    • Right-click the table (Main_Data) and click a new measure.
    • Let us call it Total Sales. After the ‘=’ sign, type in SUM(Main_Data[Sale Amt]) and hit enter.
    • As a good practice, immediately click the $ symbol and choose the currency format. (I have USD and I have also comma as thousand separator).

    Repeat the same process for Total Hours. The DAX formula for Total Hours is Total Hours = SUM(Main_Data[Hours]).

    Repeat the same process for Count_Names. The DAX formula for Count_Names is Count_Names = COUNT(Main_Data[Emp Id]).

    Finally, let us create an Efficiency measure by using the following function. Efficiency = DIVIDE([Total Sales], [Total Hours],0).

    Click on the Report Canvas tab and select the matrix visualization from the list of Visualizations. 

    In the Rows field, drag and drop the names. In the Values field, drag and drop the Efficiency.

    As we can see from the visual, 11 Names are corresponding to the efficiency of 100. 

    Now our next task is to bin employees whose efficiency is from 0-10, 11-20, etc. 

    Let us go back to the data/model view and click on the new column under the modeling tab. 

    In the formula bar input the below formula.

    BinCol = 

VAR mylbshr = [Efficiency]

RETURN
            
SWITCH (
        TRUE (),
        mylbshr <= 10, "<= 10",
        mylbshr > 10
            && mylbshr <= 20, "10 - 20",
        mylbshr > 20
            && mylbshr <= 30, "20 - 30",
        mylbshr > 30
            && mylbshr <= 40, "30 - 40",
        mylbshr > 40
            && mylbshr <= 50, "40 - 50",
        mylbshr > 50
            && mylbshr <= 60, "50 - 60",
        mylbshr > 60
            && mylbshr <= 70, "60 - 70",
        mylbshr > 70
            && mylbshr <= 80, "70 - 80",
        mylbshr > 80
            && mylbshr <= 90, "80 - 90",
        mylbshr > 90
            && mylbshr <= 100, "90 - 100",
        mylbshr > 100, " More than 100"
    )
    1. Let us break down this formula. 
      • We are declaring a variable and storing the value of the measure ([Efficiency]) in it. 
      • We are then returning the value based on the SWITCH() function.
      • We are saying that if the efficiency is <=10 then return me “<=10” else continue. 
      • This function evaluates all the rows and inputs the value based on this test in the Bin_Col column. 

    Click the Clustered Column Chart icon in the visualizations pane. Drag and drop Bin_Col in the Axis field and Count_Names in the Values field. 

    Histogram

    We have now created histogram/binning in Power BI. In the next blog post, we will create a very similar histogram but using the Disconnected Tables method. Please do let me know what you think of this. 

    Till next time !!!

  • Power BI Date Dimension Demystified Part 2

    Power BI Date Dimension Demystified Part 2

    Hello All, let us continue with our journey of demystifying the Date Dimension.

    In my last post, we learned to use CALENDARAUTO() function. We will continue today with the second DAX function called Calendar(). We will also look at how to extract other “relevant columns” such as year, month name, month number, etc. I will go over the following in this post.

    1. Create a date column using the CALENDAR() function.
    2. Use multiple DAX formulas to extract/add these columns.
    3. Use one DAX formula to generate the entire Date Table that will include all these columns.
    4. Dynamic dates selection and usage.

    CALENDAR() Function:

    The CALENDAR() functions take two parameters, StartDate, End Date, and return a table with one column with all dates between the StartDate and EndDate.

    For this exercise, let StartDate be 01/01/2020 and EndDate be 12/31/2020.

    If you follow this series from Part 1, then your environment is already set, and follow along. If you are new and want to follow the same dataset, please go through Part 1 and the Power Query. 

    Let us begin by navigating to the Modeling tab, click on the New Table, and use the following code to create a column in this table containing all the dates between the start and end date as shown below.

    date table using calendar = CALENDAR(DATE(2020,01,01), DATE(2020,12,31))

    Once you hit enter, you should have the Date Column in your model. To view it, click on the data tab on the left, and you should be a single column in this table of Date/Time datatype ranging from 1/1/2020 to 12/31/2020 as shown below.

    As you can see we have a very similar result to what we had in Part 1. Since in Part 1 we used CALENDARAUTO(), Power BI fetched the minimum (start-date) and maximum (end-date) automatically from our model. However, for the CALENDAR() function, we need to specify the StartDate and EndDate.

    Once we have this column, we can then extract important and relevant information regarding the date. We will be extracting information such as Month Number, Year, Month Name, Weekday, Quarter, Second approach to Quarter, Week Number, Day Number of the Month, WeekDay Number of the week.

    I have included all these code snippets as follows to follow along with me as I do this. After the Date Column is created (using the approach above), all we need to do is create a new column for each of the relevant details.

    To create the Month Number: 

    1. Click on the Data tab on the left on the Power BI Desktop.
    2. Once you are in the Data tab, you should see the date table we created above on the right under the field. 
    3. If you followed along, then you should have a table named “date table using the calendar.”
    4. Click on the new column icon.
    5. Enter the following formula in the formula bar. 
    6. Month Number = Month(‘date table using calendar'[Date])
    7. Press enter.
    8. Your new column named Month Number is ready.
    9. Follow the same process for Month Name, Year, Weekday, Quarter, Quarter Second Approach, Week Number, Day Number, Weekday Number.

    Use the following code snippets to generate these columns. I have included the images of the next two columns as an example. 

    We have already done the first and second below. 

    • date table using calendar = CALENDAR(DATE(2020,01,01), DATE(2020,12,31))
    • Month Number = MONTH(‘date table using calendar'[Date])
    • Month Name = FORMAT(‘date table using calendar'[Date], “mmmm”)
    • Year = YEAR(‘date table using calendar'[Date])
    • Weekday = FORMAT(‘date table using calendar'[Date], “dddd”)
    • Quarter = Var monthnum = MONTH(‘date table using calendar'[Date])

    Return 

    SWITCH

    (

                        TRUE(),

                        monthnum <=3, “Q1”,

                        monthnum > 3 && monthnum <= 6, “Q2”,

                        monthnum > 6 && monthnum <= 9, “Q3”,

                        monthnum > 9, “Q4”

    )

    • Quarter Second Approach = 

    // storing the month number in a variable called mnth

    var mnth = MONTH(‘date table using calendar'[Date])

    // getting the quarters 

    var myqrt = ((mnth -1)/3 ) + 1

    // truncate this result using Trunc function and WITHOUT passing the optional params

    var trnc = TRUNC(myqrt)

    // concat the alpahbet Q to this result 

    var addQ = “Q ” & trnc

    // Retun this

    return addQ

    • Weeknumber = WEEKNUM(‘date table using calendar'[Date])
    • Day Number of the month = DAY(‘date table using calendar'[Date])
    • Weekday number of the week = WEEKDAY(‘date table using calendar'[Date])

    You should have the following columns in your date table by now.

    Let us take a pause here and create these columns using the Part 1 approach. In that post, we used Calendarauto() function. Using the same approach let us create other columns. 

    1. Once again navigate to the Data tab. 
    2. Click the table named date table from calendarauto. 
    3. Click on the new column and enter the code snippets 

    These are the code snippets for the CALENDARAUTO() function. Please note that the start date and end date will be different for both these functions. The reason being in CALENDAR() we are specifying the StartDate and EndDate, whereas in CALENDATAUTO() it identifies the start date and end date based on the model. We have covered the CALENDARAUTO() in detail in the previous post here.

    // all the columns for the Date Table from the start

    • date table from calendarauto DAX = CALENDARAUTO()
    • Month Number = MONTH(‘date table from calendarauto DAX'[Date])
    • Month Name = FORMAT(‘date table from calendarauto DAX'[Date], “mmmm”)
    • Year = YEAR(‘date table from calendarauto DAX'[Date])
    • Weekday = FORMAT(‘date table from calendarauto DAX'[Date], “dddd”)
    • Quarter = Var monthnum = MONTH(‘date table from calendarauto DAX'[Date])

    Return 

    SWITCH

    (

                        TRUE(),

                        monthnum <=3, “Q1”,

                        monthnum > 3 && monthnum <= 6, “Q2”,

                        monthnum > 6 && monthnum <= 9, “Q3”,

                        monthnum > 9, “Q4”

    )

    • Quarter Second Approach = 

    // storing the month number in a variable called mnth

    var mnth = MONTH(‘date table from calendarauto DAX'[Date])

    // getting the quarters 

    var myqrt = ((mnth -1)/3 ) + 1

    // truncate this result using Trunc function and WITHOUT passing the optional params

    var trnc = TRUNC(myqrt)

    // concat the alpahbet Q to this result 

    var addQ = “Q ” & trnc

    // Retun this

    return addQ

    • Weeknumber = WEEKNUM(‘date table from calendarauto DAX'[Date])
    • Day Number of the month = DAY(‘date table from calendarauto DAX'[Date])
    • Weekday number of the week = WEEKDAY(‘date table from calendarauto DAX'[Date])

    Awesome !!! we now have a date table with relevant data using both Calendar and Calendarauto approach. Let us dig a little deep into these DAX formulas that we have written. If you notice, we created multiple calculated columns based on the very first date column. This is okay to do so. However, we can combine all these separate DAX formulas into one.

    For this let us create an entire date table using a single DAX formula and adding these columns within it. 

    1. Click on the modeling tab.
    2. Click on the new table icon.
    3. Paste the following code snippet.
    4. Once you press enter, your entire date table will be ready and populated. 

    Date Using DaxCal Calendar =

    // This variable is storing the column values

    VAR DaxCal =

       CALENDAR ( DATE ( 2020, 01, 01 ), DATE ( 2020, 12, 31 ) )

    RETURN

       // AddColumns returns a table with new columns specified by the DAX expressions

       ADDCOLUMNS (

         DaxCal,

         “Year”, YEAR ( [Date] ),

         “Month Number”, MONTH ( [Date] ),

         “Month Name”, FORMAT ( [Date], “mmmm” ),

         “Quarter”,

           “Q “

             & TRUNC ( ( ( MONTH ( [Date] ) – 1 ) / 3 ) + 1 ),

         “WeekNum”, WEEKNUM ( [Date] ),

         “Day of the Month”, DAY ( [Date] ),

         “Weekday Number of the Week”, WEEKDAY ( [Date] ),

         “Weekday Name”, FORMAT ( [Date], “dddd” ),

         “Full Date Only”, FORMAT ( [Date], “mm/dd/yyyy” )

       )

    You should have a date table as shown below.

    Let us break down the code.

    1. We are calling Calendar Function just like we did before. However this time we are storing the result in a Variable.
    2. We then want to return a table with other columns created using the DAX expressions.
    3. To do so we are calling the ADDCOLUMNS function.
    4. The first parameter we need to pass is the name of the table. In this case, it is our variable. I called it DaxCal.
    5. The next parameter is the name of the column. I called it Year.
    6. The last parameter is the DAX expression. In this case, it is YEAR([DATE]).
    7. Follow the same process till all the other columns have been called.

    I have included the very similar code snippet for CALENDATAUTO() function that was used in Part 1. 

    Date Using DaxCal Calendarauto = 

    VAR DaxCal =

       CALENDARAUTO ()

    RETURN

       ADDCOLUMNS (

         DaxCal,

         “Year”, YEAR ( [Date] ),

         “Month Number”, MONTH ( [Date] ),

         “Month Name”, FORMAT ( [Date], “mmmm” ),

         “Quarter”,

           “Q “

             & TRUNC ( ( ( MONTH ( [Date] ) – 1 ) / 3 ) + 1 ),

         “WeekNum”, WEEKNUM ( [Date] ),

         “Day of the Month”, DAY ( [Date] ),

         “Weekday Number of the Week”, WEEKDAY ( [Date] ),

         “Weekday Name”, FORMAT ( [Date], “dddd” ),

         “Full Date Only”, FORMAT ( [Date], “mm/dd/yyyy” )

       )

    Though we have created a Date Table, we are not done yet. Let us improve on this approach. What we just did is what I call the brute force method of creating a Data Table using the Calendar function. What I mean by this is that we hardcoded the StartDate and EndDate values in this.

    Let us take this further and make it dynamic. If you remember from my previous post, There are 3 Main Things we need. StartDate, EndDate, and Duration. Once again I will reiterate that please remember this as this will help us understand the concept of Date Table when we create the Date Dimension using M Query in the future posts.

    By now we know that the function CALENDAR() requires StartDate and EndDate. For now, we handed it by providing static values. Let us make it more dynamic.

    Let us say, that we needed our date table to start from the first Order date as the StartDate and the last ship date as the EndDate.

    To do so we will declare two more variables as follows.

    Date Using DaxCal Calendar Dynamic =
    // we are getting the minimum of the Order Date and storing it in a variable
    Var MinDate = MIN(OrdersData[Order Date])

    // we are getting the latest Ship Date and storing it in a variable
    Var MaxDate = MAX(OrdersData[Ship Date])

    // Calling the Calendar function and passing into it, the two variables as the arguments and returning the result with relevant columns
    VAR DaxCal =
    CALENDAR(MinDate,MaxDate)
    RETURN
    ADDCOLUMNS (
    DaxCal,
    “Year”, YEAR ( [Date] ),
    “Month Number”, MONTH ( [Date] ),
    “Month Name”, FORMAT ( [Date], “mmmm” ),
    “Quarter”,
    “Q “
    & TRUNC ( ( ( MONTH ( [Date] ) – 1 ) / 3 ) + 1 ),
    “WeekNum”, WEEKNUM ( [Date] ),
    “Day of the Month”, DAY ( [Date] ),
    “Weekday Number of the Week”, WEEKDAY ( [Date] ),
    “Weekday Name”, FORMAT ( [Date], “dddd” ),
    “Full Date Only”, FORMAT ( [Date], “mm/dd/yyyy” )
    )

    With this we were able to use two DAX functions, CALENDAR() and CALENDARAUTO(), to create the date table with additional columns as needed. I hope that I was able to shed some light on these two DAX functions. In the file below you will find all the code snippets that we used thus far.

    All the columns for the Date Table using CALENDAR()

    // all the columns for the Date Table from the start -- CALENDAR()


date table using calendar = CALENDAR(DATE(2020,01,01), DATE(2020,12,31))

Month Number = MONTH('date table using calendar'[Date])

Month Name = FORMAT('date table using calendar'[Date], "mmmm")

Year = YEAR('date table using calendar'[Date])

Weekday = FORMAT('date table using calendar'[Date], "dddd")


Quarter = 
            Var monthnum = MONTH('date table using calendar'[Date])
            Return 
                SWITCH
				(
				
                    TRUE(),
                    monthnum <=3, "Q1",
                    monthnum > 3 && monthnum <= 6, "Q2",
                    monthnum > 6 && monthnum <= 9, "Q3",
                    monthnum > 9, "Q4"
                )


Quarter Second Approach = 

// storing the month number in a variable called mnth
var mnth = MONTH('date table using calendar'[Date])

// getting the quarters 
var myqrt = ((mnth -1)/3 ) + 1

// truncate this result using Trunc function and WITHOUT passing the optional params
var trnc = TRUNC(myqrt)

// concat the alpahbet Q to this result 
var addQ = "Q " & trnc

// Retun this
return addQ



Weeknumber = WEEKNUM('date table using calendar'[Date])

Day Number of the month = DAY('date table using calendar'[Date])

Weekday number of the week = WEEKDAY('date table using calendar'[Date])

    All the columns for the Date Table using CALENDARAUTO()

    
// all the columns for the Date Table from the start -- CALENDARAUTO()


date table from calendarauto DAX = CALENDARAUTO()

Month Number = MONTH('date table from calendarauto DAX'[Date])

Month Name = FORMAT('date table from calendarauto DAX'[Date], "mmmm")

Year = YEAR('date table from calendarauto DAX'[Date])

Weekday = FORMAT('date table from calendarauto DAX'[Date], "dddd")

Quarter = 
            Var monthnum = MONTH('date table from calendarauto DAX'[Date])
            Return 
                SWITCH
				(
                    TRUE(),
                    monthnum <=3, "Q1",
                    monthnum > 3 && monthnum <= 6, "Q2",
                    monthnum > 6 && monthnum <= 9, "Q3",
                    monthnum > 9, "Q4"
                )


Quarter Second Approach = 

// storing the month number in a variable called mnth
var mnth = MONTH('date table from calendarauto DAX'[Date]
)

// getting the quarters 
var myqrt = ((mnth -1)/3 ) + 1

// truncate this result using Trunc function and WITHOUT passing the optional params
var trnc = TRUNC(myqrt)

// concat the alpahbet Q to this result 
var addQ = "Q " & trnc

// Retun this
return addQ


Weeknumber = WEEKNUM('date table from calendarauto DAX'[Date])

Day Number of the month = DAY('date table from calendarauto DAX'[Date])

Weekday number of the week = WEEKDAY('date table from calendarauto DAX'[Date])

    In the next blog post, we will create Date Dimension using Power Query. If you have questions/queries/comments please do not hesitate to reach out to me.

    Till next time !!!!

  • Power BI Date Dimension Demystified Part 1

    Power BI Date Dimension Demystified Part 1

    Hello All, today, let us take the first step towards demystifying the Date Table journey. Date Dimension plays a vital role.

    Almost always, there is a need to perform and derive time-based insights. Few ground rules must be adhered to when it comes to creating a robust date table. These are :

    • The date table must contain unique values
    • The date table must contain no null values
    • The date table must contain contiguous date values (from beginning to end)

    This series of articles will go over different manipulations/iterations of how one can achieve this.

    For any date table, we must have 3 values/properties present: the start date, the end date, and the duration. For these articles, let us call them 3P, as we will be revisiting them.

    The start date — This the first (earliest) date available. We can either enter this manually (using the calendar() function — coming up in the next article) or calculated it based on the model. 

    The end date — This is the last (latest) date available. We can either enter this manually (using the calendar() function — coming up in the next article) or calculated it based on the model. 

    The duration — This is the increment or step. Almost always, this increment is “day.” For example, let us consider the month of June 2020. 

    The start date would be 06/01/2020 — 1st of June 2020

    The last date would be 06/30/2020 — 30th of June 2020

    The duration would be each day. Therefore if we were to create a date-table, we should have 30 unique dates for June 2020. 

    Let us begin with the CALENDARAUTO() DAX function. 

    This function returns a table with a single column named “Date” containing a contiguous set of dates. The range of dates is calculated automatically based on data in the model. 

    This function has some considerations/rules associated with it, they are :

    1. The model MUST contain date-time values. 
    2. It does not take into account the date values based on the calculated table and calculated column.
    3. The earliest date is calculated based on the MinDate 
    4. The latest date is calculated based on the MaxDate 
    5. If we pass an argument (an integer between 1 and 12), then our date table range “shifts” by that step. 

    Let us begin with our sample data. 

    superstore_sample_dataDownload

    My other post (Power Query) talks about how to connect to this data. I will go over this very quickly once again. But if you need to revisit Power Query, click the icon below to do so.

    1. Click get data
    2. Click on Excel
    3. Navigate to the folder where the excel file resides
    4. Load the data (usually, we would go with transform data as this is a good practice to ensure the quality is consistent. However, this sample data is the right format as we need it to be in). 
    5. Navigate to the modeling tab and click on the new table
    6. This should open the formula bar.
    7. Name the table (I named it date table from calendarauto DAX and type in calendarauto(). My final formula is date table from calendarauto DAX = CALENDARAUTO() )
    8. This will create a date-table with a single column called date, as seen below.
    9. As you can see, it took the MinDate, MaxDate, and duration automatically based on the rules above. 
    10. We did not need to pass any parameter as, by default, the parameter (fiscal year-end month) is 12 (December)
    Power query navigation

    Now let us supply 3 as the argument to our function and see the “shift” in action. 

    1. Navigate to the modeling tab and click on the new table
    2. This should open the formula bar.
    3. Name the table (I named it date table from calendarauto with 3 and type in calendarauto(3). My final formula is the date from calendar auto with 3 = CALENDARAUTO(3) )
    4. This will create a date-table with a single column called date, as seen below.
    5. As you can see, it took the MinDate, MaxDate, and duration automatically based on the rules above. 
    6. We passed 3 as a parameter, and hence the MinDate became the end of 3rd month for the year before. Our original date started from 1/1/1995 and ended on 12/31/2025; we shifted the start date by providing 3 as the parameter. As per our rule discussed earlier, the MinDate is calculated based on the fiscal year-end month. 
    7. The fiscal year-end month is nothing but the parameter that we provide. Since, by default, the parameter (fiscal year-end month) is 12 (December) hence the MinDate was 1/1/1995. In this case, we provided 3 as the parameter, so our start date should become 4/1/1994, and keeping the reference intact, the end date is the end of March, i.e., 3/31/2026

    Our work here is not done yet. We have just a single column with date-time values. However, this is by far the most important piece if creating the entire date table. Once we have unique, non-null, date-time values (remember our rules above), we can extract all the other information out of it.

    However, this is all we need to extract all the other information out of it. The steps to extract the various values such as month name, month number, day number, etc., will be covered in the next post when we demystify the calender() function since extracting the information is the same. 

    I hope that I was able to demystify this for you. However, if you have any concerns/queries/doubts or feel that I have made an error about this, please do not hesitate to leave a comment or send me the feedback. I will be more than happy to discuss this. 

  • Introduction

    The big question: What exactly is Power BI?

    Short answer: A data visualization tool.

    Long answer: Though primarily it is a data visualization tool, Power BI is a very powerful self service BI tool. It is made up of multiple components and they all work in tandem to provide you with a wholesome data discovery experience.

    The usual flow of data in Power BI is from fetch data from Sources (both online and on cloud) to Power BI Desktop (though the data can be fetched directly into Power BI service too), model the data, transform the data, apply logic, apply calculations and then finally publish the data to Power BI Service for others to see, collaborate or create content on top of it.

    To achieve this Power BI makes use of various components such as Power Query, Power BI Desktop, Power BI Service, Gateways, Power Shell to name a few. Please visit the links to get in depth know how on individual components.

  • PowerShell – Adding a Dataset

    PowerShell – Adding a Dataset

    PowerShell Scripts for creating and adding a dataset, columns and table. 

    Add PBIDataset

New-PowerBIColumn ---- creates a column
New-PowerBITable ----- creates a table
New-PowerBIDataSet ---- creates a dataset
Add-PowerBIDataset ----- adds a dataset


$ is used to declare a variable

Creating a Dataset

PS C:\"your path here"> $col1 = new-powerbicolumn -Name Name -Datatype String
>> $col2 = New-PowerBIColumn -Name ID -DataType Int64
>> $col3 = New-PowerBIColumn -Name Gender -DataType String
>> $table_primary = New-PowerBITable -Name main_table -Columns $col1,$col2,$col3
>>
>> $col4 = New-PowerBIColumn -Name ID -DataType Int64
>> $col5 = New-PowerBIColumn -Name Date -DataType DateTime
>> $col6 = New-PowerBIColumn -Name Detail -DataType String
>> $col7 = New-PowerBIColumn -Name Result -DataType Double
>> $table_secondary = New-PowerBITable -Name secondary_table -Columns $col4,$col5,$col6,$col7
>>
>> $dataset = New-PowerBIDataset -Name Meansh_Sample -Tables $table_primary, $table_secondary
>>
>> Add-PowerBIDataset -Dataset $dataset -WorkspaceId "provide workspaceID"


{

	$ is used to define a variable 
	new-powerbicolumn --- inbuilt cmdlet
	new-powerbitable --- inbuilt cmdlet
	new-powerbidataset --- inbuilt cmdlet
	
	
define a columns first, requires name and datatype

define a table and required which columns will be part of it

define a dataset and requires which tables will be part of it as well as the name of the data set 
	
	Finally do 

Add-PowerBIDataset command and provide the id for the workspace ----- if no name is given then the dataset will be created in "MyWorkspace" 

else if the name is provided then it will be a part of that workspace.
	
}	


 $col1 = New-PowerBIColumn -Name ID -DataType Int64
>> $col2 = New-PowerBIColumn -Name Data -DataType String
>> $table1 = New-PowerBITable -Name SampleTable1 -Columns $col1,$col2
>> $col3 = New-PowerBIColumn -Name ID -DataType Int64
>> $col4 = New-PowerBIColumn -Name Date -DataType DateTime
>> $col5 = New-PowerBIColumn -Name Detail -DataType String
>> $col6 = New-PowerBIColumn -Name Result -DataType Double
>> $table2 = New-PowerBITable -Name SampleTable2 -Columns $col3,$col4,$col5,$col6
>> $dataset = New-PowerBIDataSet -Name SampleDataSet -Tables $table1,$table2

  • Custom Scripts – Power Shell

    Custom Scripts – Power Shell

    One of my close friend had this discussion with me about how to get list of workspaces with certain conditions and how to get those details using PowerShell script. So this following script was written to get conditional workspaces and displaying them.

    While we were testing this, we realized that there was an error thrown due to number of calls the script was making. To handle that we can add start – stop as a gap timer to this to ensure there is no issue.

    $worksapces = Get-PowerBIWorkspace -Scope Organization -All

Write-Host "Getting Workspaces"

$groupnames = @()

ForEach ($workspace in $worksapces) 
{if ($workspace.Type -eq "PersonalGroup") { continue }
if ($workspace.IsOnDedicatedCapacity) { continue }

$reports = Get-PowerBIReport -WorkspaceId $workspace.Id  -Scope Organization

if ($reports.Length -eq 0) { continue }

if ($reports.Length -gt 0) {
    Write-Host "$($workspace.Name) - $($workspace.Type) - $($workspace.State) - $($workspace.IsOnDedicatedCapacity) - $($reports.Count)"
}

$exportWS = New-Object -TypeName PSObject -Property @{
    Name = $workspace.Name
    Type = $workspace.Type
    State = $workspace.State
    IsOnDedicatedCapacity = $workspace.IsOnDedicatedCapacity
    ReportCount = $reports.Count
}
$groupnames += $exportWS
}
$groupnames | Export-CSV "you_chosen_path_entire_path_is_needed.csv"
  • Powershell Commands

    Powershell Commands

    Following modules are available

    MicrosoftPowerBIMgmt — is the highest  level within this you can have following

    1.Admin

    2.Capacities

    3.Data

    4.Profile

    5.Reports

    6.Workspaces

    To get staretd

    Install-Module -Name MicrosoftPowerBIMgmt and then run the installation command for individual modules as needed

My journey from Datum to Data