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 @ZeeScience,

If it helps, this is the DAX formula I have used for my dimDate table. I have also selected 'Mark as date table' was the table is created.

dimDate = ADDCOLUMNS(
    CALENDARAUTO(),
    "MonthID", MONTH([Date]),
    "Month", FORMAT([Date], "MMM"),
    "DayNumber", DAY([Date]),
    "DayName", FORMAT([Date], "dddd"),
    "DayID", WEEKDAY([Date], 2),
    "Year", YEAR([Date]),
    "Today", TODAY(),
"Report Refresh",Now() //We'll use this to display the report refresh date
)

I then created a Many to One (N:1) relationship from the Animal table to the dimDate table.

'Date of Arrival' to 'Date'.

[ZeeScience]

@Corry 

My Date disappears once I add a field into the columns field. I am unable to filter with the Date neither am I able to use the dates to see when animals arrived in their respective years. See attached screenshot

[ZeeScience]

@Corry 

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

What does [Total Animals] represent 

[Corry]

Hi @ZeeScience,

Based on the image you supplied, you have no values added for that visual. Think of an excel spreadsheet, you have the Rows and Columns defined but not values in the cells for those rows and columns.

Here is an example with values added.

Also, you may want to check how you are defining your rows, I see you have FiscalYear Hierarchy. I believe the requirements are by months so you may not need the Fiscal Year part.

[Corry]

@ZeeScience, this was given to me as the default and only option when creating the formula. I would say it represents what it says. A total of my animals table. So, in the context of this formula. It is creating a calculation that will give me a total of all shelter status's that = "Adopted". At least this is how I am interpreting it; I could be wrong. I know nothing about DAX language.

[ZeeScience]

@Corry 

Even when I added values, my date still disappears. I do not understand what is going on again.

[ZeeScience]

@Corry 

Also, how did you do the calculation for getting animals that were fostered because I was thinking Animas with the following status: Claimed for Adoption, Ready for Adoption and In Foster Home, are all animals that have been fostered. How do you think I can go about getting the amount of animals that were fostered

[Grkm_A]

Hi,

I have same problem. The date information of dimDate table can not connect with the Animal table. 

When ı use Date of Arrival on Rows I can get the date information. In my opinion the relationship is not working but set up f relationship seems correct to me. I shared the set up below too. 

Relationship between Animal and Date tables:

[Poovarasan27]

@Corry - Your solution worked for me. I have created 3 measure columns for total animal, total animals rescued , total animals fostered.

But i am facing trouble in creating a measure column for last month or 3 months of for above column. In the video, they are using a function ' sameperiod this year '. 

kindly help me out ?

[PratikJ]

What to do after the refresh step?