Create measure from multiple related table (3 table's involved)

[Jimny]

Hi,
I need some help to create a measure in Power BI. I can’t use “Cross filter direction”: “Both” in the join between tables, because it’s not supported by “SQL Server 2014” in the tabular model cube.

Background info

Table: Subsctiption
We have Subscription with a certain SubscriptionID, SubscriptionStartDate and SubscriptionEndDate, at Subscription table.

Table: SubscriptionVersion
It is possible to update a Subscription. For example; when the productID is changed. When this happens, the SubscriptionID remains the same, but a new SubscriptionVersion line is added to the SubscriptionVersionTable. With new SubscriptionVersionStartDate and SubscriptionVersionEndDate.

I managed to create a measure:
RunTotalSubscriptionVerStarted = CALCULATE([CountSubscriptionVerStart];FILTER(ALL('Time'[PK_Date]);'Time'[PK_Date]<=MAX('Time'[PK_Date])))
This measure calculates the running total of started SubscriptionVersion, from SubscriptionVersion table. By selecting a PK_Date from Time table.

Table: ProductInstanceUsage
This table give the usage of “TotalMB’s” by Date, by SubscriptionID.

I managed to create a measure:
SubscritionVersion = CALCULATE(max(SubscriptionVersion[SubscriptionVersion]);FILTER(SubscriptionVersion;ProductInstanceUsage[SubscriptionID]=SubscriptionVersion[subscriptionID]&&ProductInstanceUsage[Date]>=SubscriptionVersion[SubscriptionVersionStartDate]&&if(ISBLANK(SubscriptionVersion[SubscriptionVersionEndDate]);1-1-2070;ProductInstanceUsage[Date]<SubscriptionVersion[SubscriptionVersionEndDate])))
This measure calculates the “SubscriptionVersion” from the “SubscriptionVersion” table, by “SubscrtiptionID”, between “SubscriptionVersionStartDate” and “subscriptionVersionEndDate”

Table: Time
Is a time table I have simplified for this case example.

Issue:
How can I create a measure at the SubscriptionVersionTable: that will give me ProductInstandeUsage[TotalMB’s], by given period from Time[PK_Date], by selecting SubscriptionVersion[SubscriptionProductId]

[v-shex-msft]

Hi,

>> How can I create a measure at the SubscriptionVersionTable: that will give me ProductInstandeUsage[TotalMB’s], by given period from Time[PK_Date], by selecting SubscriptionVersion[SubscriptionProductId]
According to your description, you want to get the sum of ProductInstandeUsage[TotalMB’s] based on choosed “Time[PK_Date]” and “SubscriptionVersion[SubscriptionProductId]”, right?

You can refer to below measure to get the specify sum of ProductInstandeUsage[TotalMBs]:

Sum of Specify SubscriptionID and Date = CALCULATE(SUM(ProductInstanceUsage[Sum of TotalMBs]), FILTER(ProductInstanceUsage,ProductInstanceUsage[SubscripitonID]= if(HASONEVALUE(SubscriptionVersion[SubscriptionID]),LASTNONBLANK(SubscriptionVersion[SubscriptionID],SubscriptionVersion[SubscriptionID]),BLANK())&&ProductInstanceUsage[Date]=if(HASONEVALUE(DateTable[Date]),MAX(DateTable[Date]),BLANK())))

Slicers:

Result:

Notice: this measure only works when you choose one item.

Regards,
Xiaoxin Sheng

[Jimny]

Hi Xiaoxin,

I used the measure in Power BI, see below.

SumTotalMBsUsage = CALCULATE(SUM(ProductInstanceUsage[TotalMBs]); FILTER(ProductInstanceUsage;ProductInstanceUsage[SubscriptionID]= if(HASONEVALUE(SubscriptionVersion[SubscriptionID]);LASTNONBLANK(SubscriptionVersion[SubscriptionID];SubscriptionVersion[SubscriptionID]);BLANK())&&ProductInstanceUsage[Date]=if(HASONEVALUE('Date'[Date]);MAX('Date'[Date]);BLANK())))

I got data by selecting "MainVoiceProduct" = 114, and "SubscrtiptionID" = 1114, for 4 dates in the Timetable. It seems like

I get the sum of "TotalMBs" where for the SubscriptionVersion[SubscriptionID] <> ProductInstanceUsage[SubscriptionID]

It should be the otherway around where SubscriptionVersion[SubscriptionID] = ProductInstanceUsage[SubscriptionID].

So the output should be 8.14 instead of 2512.87