Using TREATAS to dynamic budget granularity

[Minyun]

Hi,

I am new to PowerBI and after reading through several articles I am still trying to wrap my head around the TREATAS function.

I have created a BudgetTable as below, listing budget values ('Value') per YearMonth (for the 2021 period) for various business branches ('Depot') and associated business types ('BusType'). I have also summed these values with a measure ('TotalValue')

I have created a DateTable with the columns listed below **Note that this DateTable ranges from 2020-01-01 to 2021-21-31 (again BudgetTable only lists budget values for the 2021 period).

I have created a measure (BudSplit) within my fact table (spRptPBIUnpackBudget-which is imported from MS SQL) as per the below. This works no problem, showing me budgets per MonthName as expected.

...however the problem is that when I drill down to the relevant sub-dimensions (WeekNumber and DayNumber) the measure does not perform as expected as per the below...

...the BudSplit measure is still showing values for MonthName rather than WeekNumber and/or DayNumber.

Finally my table relationships are as per the below.

I suspect that this has something to do with my TREATAS DAX but am unsure how it should be modified to accommodate the sub-dimensions.

Any advice or help would be greatly appreciated.

Thank you.

[v-cazheng-msft]

Hi @Minyun 

Your description is quite clear and I've got what your problem is. Could you please tell me how you create relationships between Date table and table from MS SQL? If convenient, could you please provide a link of upload sample pbix file so we can do research on it to help you find the solution? Thanks in advance!

Best Regards,

Community Support Team _Caiyun Zheng

[Minyun]

Hi,

Thank you for responding. I am unable to share the .pbix.

The relationship between DateTable and MS SQL is using an additional column called DateJoin

DateJoin =

FORMAT(DateTable[Date], "YYYY")

&"-"&FORMAT(DateTable[Date], "MM")

&"-"&FORMAT(WEEKNUM(DateTable[Date],2),"00")

&"-"&FORMAT(DateTable[Date],"DD"))

This allows me to create 1 -> * relationship through all of my fact tables.

[v-cazheng-msft]

@Minyun 

Sorry for the late response. I’ve tested at my side based on the information from you, but I can drill down with this Measure. Not sure whether there is any other factor caused this issue.

Attached sample pbix file, it may help you find the cause.

Best Regards,

Community Support Team _ Caiyun

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