Waterfall chart - seems not to work when based on a calculated column

[pkd]

Hi,

I'm trying to use Waterfall Chart to show how many employees joined our company when. I use a date hierarchy so that I can drill down from showing it by-year to by-day. My model is very simple:

1) table "Dates" - it's a function-generated table with the date hierarchy; there's a row for every day in the past couple of years

2) table "Employees" - table with Employee identifier and date of joining the company

3) measure TotalEmployees = COUNTROWS(Employees)

4) relation 1-to-many: Dates.Date --> Employees.DateOfJoining

With such a setup everything works fine. I setup my Waterfall Chart putting "Date" as a Category and "TotalEmployees" as Y Axis.

The problem

What I actually need to do is to calculate DateOfJoining. Whenever I try to use a calculation for the DateOfJoining (have a calculated column instead of a normal one), my Waterfall Chart gets broken. I experimented a lot with this in order to present a simple way of reproducing it for this post's sake. All in all the chart gets broken after the following steps:

1) I create another table tempEmployees looking identical to original Employees with persisted DateOfJoining (I load it from csv again)

2) I create a 1-1 relationship between Employees and tempEmployees (on employee identifier)

3) I extend table "Employees" with a calculated column "myLookupDateOfJoining". myLookupDateOfJoining = RELATED(tempEmployees[DateOfJoining]). Obviously Employees.myLookupDateOfJoining becomes populated with values from tempEmployees, identical to the original DateOfJoining.

4) I don't do anything to my measure (because it counts rows in Employees anyway) or to the chart itself (because it uses a measure and Dates table which I haven't touched).

5) as a final step I switch 1-to-many relationship: Dates.Date --> Employees.myLookupDateOfJoining

... and my chart becomes broken. It no longer shows data by year, it just shows 211 in a single bar. Actually, because for some employees I have NULL date, it shows 2 bars: (Blank)->211 and Total->211. 

It seems to me that somehow Waterfall Chart does not work with a calculated column. Or the column taking data from another table? I haven't found anything on-line that would discuss such a limitation so maybe there's something obvious I cannot see.

Thank you in advance for your help,

PKD

[v-yuta-msft]

@pkd ,

This issue should be caused by the new create columns using RELATED() function as a condition. You can use LOOKUPVALUE() instead of RELATED() in calculate column and check if it works.

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[pkd]

Hi @v-yuta-msft ,

Thank you for your attempt at solving my issue. It does not work unfortunately. I used LookupValue function for a calculated column as you specified. I can also confirm that I now have 2 columns (the original, non-calculated one and the LookupValue column, calculated one) which display EXACTLY the same values for every row. Once I switch my relationship (Dates) from the non-calculated one to the calculated one, the chart breaks. I switch back - it fixes itself (see the screenshots from the original post).

I'll be happy to include the pbix if needed. Also, I'll be happy to download your pbix if you have something like this working.

PKD