virtual relationship with treatas

[cmcubism]

Hi there,

I have the need to create a very flexible report that switches between different time periods as there are:

- A normal busines year 01.01. until 31.12. (BY)

- A modified year period, starting at 01.04. and ending at 31.03. the following year (UWY)

For this I created a detached slicer including a date table table based on this article:

https://community.powerbi.com/t5/Desktop/Dynamic-Column-calculation-based-on-Slicer-Selection/m-p/57...

With the slicer I am filtering a separate date table (DIM Date Report) that has no relation to any of the fact tables.

Now I am using either the new TREATAS or the older INTERSECT DAX functions to create measures using a virtual relationship to this date table.

The basic measures (sums) and also YTD measures (two different due to the different year periods) work really fine.

One measure though using a simple ALL functions does not work the way I expected.

I am now giving you three examples of the "normal" measure using a real relationship based on the normal date table and the corresponding measure using the virtual relationship to the special date table. The last one I am having problems with.

1. Basic sums

Real Relationship

Plan = SUM('FACT Plan'[Plan USD])

Virtual Relationship:

Plan Report = CALCULATE('FACT Plan'[Plan]; INTERSECT(ALL('DIM Date'[Date]);VALUES('DIM Date Report'[Date])))

Or the same with TREATAS

Plan Report = CALCULATE('FACT Plan'[Plan]; TREATAS(VALUES('DIM Date Report'[Date]);'DIM Date'[Date])))

This works fine and shows the corect sum when a month is filtered.

2. YTD sums

Real Relationship

Plan YTD BY = CALCULATE(SUM('FACT Plan'[Plan USD]);DATESYTD('DIM Date'[Date]))

Virtual Relationship:

Plan YTD Report =

IF(FIRSTNONBLANK('FILTER Year'[Format];"")="BY";

CALCULATE('FACT Plan'[Plan YTD BY]; INTERSECT(ALL('DIM Date'[Date]);VALUES('DIM Date Report'[Date])));
CALCULATE('FACT Plan'[Plan YTD UWY]; INTERSECT(ALL('DIM Date'[Date]);VALUES('DIM Date Report'[Date]))))

Here I had to add FIRSTNONBLANK to choose between the time periods. This works also fine. The sums upt to the selected month are all correct.

3. Full year figures with ALL() Filter

Real Relationship:

Plan full year = CALCULATE(SUM('FACT Plan'[Plan USD]);ALL('DIM Date'[Month short]))

Virtual Relationship:

Plan full year Report = CALCULATE(CALCULATE('FACT Plan'[Plan];ALL('DIM Date'[Month short]));TREATAS(VALUES('DIM Date Report'[Date]);'DIM Date'[Date]))

And here the measure shows the same amount when filtered by month as the first basic measure (see 1.)

I do not understand why the date filters are correctly propagated in case of the YTD calculation and not in this easier case.

Can anyone help? Any input is highly appreciated!

Kind regards

Christian

[v-yuezhe-msft]

@cmcubism,

Could you please share the PBIX file for us to analyze? You can upload PBIX file to OneDrive and post shared link here.

And what is your expected result using the last measure?

Regards,
Lydia

[cmcubism]

Hi Lydia,

thank you very much for your reply! I already thought nobody will have an answer to my question. 😉

Unfortunately the pbix file contains almost the complete data model of my company so I cannot share without immensively stripping it down. I will have a look into that.

Fort the time being I can explain my expected results. The first two measures already show the wanted figures.

1. Plan (planned premium written) summed per selected month

2. Plan YTD = aggregate of plan figures per all previous months up to the selected month.

3. Plan full year = sum of plan figures for all months of selected year (regardless of the month filtered).

Hope this helps to get a better picture,

Kind regards

Christian

[v-yuezhe-msft]

@cmcubism,

Could you please share PBIX file with dummy data?

Regards,
Lydia

[cmcubism]

Dear Lydia,

thank you for your efforts.

Unfortunately I won't be able to strip down the data model in the nearest future due to a tight deadline.

I found a workaround calculating the desired figure in some other way.

I will try to catch up with this topic after my deadline because I am very curious to get to the root of this problem.

Kind regards and sorry for the delay

Christian