Power BI Report for Final Challenge

[TianaFash]

Hi All,

I am stuck with the Power BI report and unsure how to navigate the Dimdate, including connecting it to the Animal table and using the VAR X. I am also unable to filter the dates to connect them with the table.

I would appreciate any guidance.

@BCBuizer @Corry, @AhmedSalih @Rebecca-MSFT 

[Corry]

Hi @TianaFash,

I found the Learning Path videos for Power BI really useful in configuring my tables and data for the Animal Shelter solution. Obviously, I had to adjust to suit my solution but it's a great starting point.

Videos 8. Create a date dimension for my report, 9. Update the data model for my report, and 11. Add measures to my report were instrumental to the process which made the data make sense.

I focused on the follow videos.

6. Connect to Students data

7. Connect to Loans data

8. Create a date dimension for my report

9. Update the data model for my report

11. Add measures to my report

12. Create visuals for my report

13. Add filters to my report

[TianaFash]

Thank you @Corry I have watched the videos particularly the one focused on creating the date dimension for the report which is where I am stuck. Hence, the reason for reaching out.

[StineN]

Hello @TianaFash!

I also struggled a lot with the date table. I tried a lot of different approaches, and it finally worked without me knowing exactly what did the trick. So I'm not sure if I'm able to help, but here's a list of the main things I did:

- I created a date table using the DAX function AddColums() and Calendar(), instead of the CalendarAuto(). This made it possible to specify the start and end date. I used the Date of Arrival in the Animal table as the minimum date, and the Date of Adoption as the maximum date. 

- Make sure that the table contains all of the dates from start to end that is in the Animals table 

- Make sure that you mark the table as a date table: Table Tools > Mark as date table > Choose Date as the column

- I then created a new date hierarchy with year and month, because the default one disappears when you mark the table as a date table. I use this one in the visuals.

- Change the data type of all the columns containing dates to Date, in the Animal table (and I also did it in all of the other tables to be sure). You must do this both in the Table view for the loaded data and in the Transform data view (edit query).

- Create a relationship between Date in the date table and Date of Arrival in the Animal table (or the one that you based the date table on)

- Refresh the data: Home > Refresh 

Hope this can be of some help!

[ZeeScience]

@TianaFash 

Below is the code I used in DAX that does not have the CalenderAuto

dimDate = ADDCOLUMNS(

    CALENDAR(DATE(2021,01,01),DATE(2023,08,30)),

    "MonthID",Month([Date]),

    "Month",FORMAT([Date], "MMMM"),

    "Day", DAY([Date]),

    "DayName", FORMAT([Date], "dddd"),

    "Year", YEAR([Date]),

    "Today", TODAY(),

    // Adding a Fiscal Type of Date

    "FiscalYear", IF

    (

        MONTH([Date]) >= 9,

        YEAR([Date])+1,

        YEAR([Date])

    ),

    "FiscalMonthID", IF

    (

        MONTH([Date]) >= 9,

        Month([Date]) - 9,

        Month([Date]) + 3

    ),

    "Report Refresh", NOW()

)

This website helped me understand better Creating Calendar Table in Power BI using DAX Functions - RADACAD

[Poovarasan27]

@Corry - Thanks for the suggestion. Can you let us know which are the columns and table that needs to be imported in power bi report for our scenario. 

While trying to import data, for choice columns like shelter status, sex, etc the data is showing the values (1,2,3) rather than the label ( male or female, adopted or claimed for foster ). kindly provide your brief insight on this. We are at the final stage of the challenge.

@ZeeScience  and @StineN - I believe you guys have completed this as well. Your insight is also might appreciated.

[Nickie_]

@StineN and @Corry  I have tried all the solutions above and I am still stuck. 

What I am having issues with is the count of animal type , where once the relationship is added all the fields disappear. I have changed the data types and matched them in both tables. 

Could it be because of the data type?

[Corry]

Hi @Nickie_.

To get the total animals rescued by type, I am using a Measure (calculation).

Total Animals Rescued = CALCULATE([Total Animals], cr0f4_animals[Shelter Status] = "Adopted")

I then have this applied to a bar chat visual in the Y-axis and I have Animal Type from my Animal table in the X-axis.

[ZeeScience]

@Poovarasan27 I am almost done with it, still having a little issues with the Date Dimension

[Corry]

Hi @Poovarasan27,

I used the Direct Query option for my Animal table, and I transformed the data to only include the following columns. I have only added the Animals table, I felt I didn't need the others for the requirements as the information is already in the Animal table.

I have just tested the import option for my Animal table, and I see 2 columns for sex, as an example.

You obviously want the sexname option so you can transform your data to no include the columns with numbers etc.