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]
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
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
