Forum Discussion

Jimny's avatar
Jimny
Regular Visitor
9 years ago

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

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]

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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's avatar
      Jimny
      Regular Visitor
      9 years ago

      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