Forum Discussion

Paco-MK2's avatar
Paco-MK2
New Member
4 years ago

Issue calcultated measures analysis services tabular

Hi all, 

 

We have an issue regarding our Power BI reports that are based on a analysis services tabular instance (compatibility level 1500). 

 

In the cube there are a few measuregroups/tables that are joined on a company dimension based on a Company_ID. The join(s) itself are pretty straigthforward and seem to work when you analyse the cube in Excel, SSMS and Power BI. 

 

We have created a few Power BI reports based on the data from this cube, but we seem te be getting the following issue since about 2 weeks.

 

When we ceate a calculated measure based on another calculated measure there is no data when we use the company_ID for filtering. For example:

 

We have a calculted measure named: "Net margin", this is calculated on the "amount" measure from the transactions fact. When we create a new calculated measure "Net Margin YTD" based on the "net margin" measure it does not seem to work when the company filter is used in the report. When we remove the company filter from the report the measure (Net Margin YTD) suddenly works.

 

It get's a little bit weirder... When we remove a relationship in the cube with a specific measuregroup/table that is not related to anything in the report the measure is working. But when we use this relationship in other reports, or Excel the relationship works fine.

 

The cube and the reports itself where not changed since we found the issue, could there be an issue with the calculated measures? 

 

Thank you!

 

 

 

 

Need Help

4 Replies

  • v-janeyg-msft's avatar
    v-janeyg-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi, Paco-MK2 

     

    I still can't understand your situation very clearly through your text description, You can check whether the created measure satisfies the conditions:

    When you use SSAS MD, You should know that: 

    • You can't create report level measures when authoring a report that is connected live to an SSAS multidimensional model. The only measures that are available are measures defined in the MD model.

    • Multidimensional models support dimension and cell level security by way of roles. When you connect to a cube with Power BI, you're authenticated and evaluated for appropriate permissions. If a user has dimension security applied, the respective dimension members aren't seen by the user in Power BI. However, when a user has defined a cell security permission where certain cells are restricted, that user can't connect to the cube using Power BI.

    Analysis Services multidimensional data in Power BI Desktop - Power BI | Microsoft Docs

     

    Did I answer your question? Please mark my reply as solution. Thank you very much.
    If not, please feel free to ask me.

    Best Regards,
    Community Support Team _ Janey

    • Paco-MK2's avatar
      Paco-MK2
      New Member
      4 years ago

      Hi,

       

      Thank you for your time! 

       

      It is a Tabular Model not Multidimensional Model.

       

      The weird thing is that the user can create calculations in Power BI, but the error only occurs when a calculation is made on a calculation AND a specific relation is selected. The relation works when the measure itself or a calculation is made on the measure. It does NOT work when a calculation is made on the calculation.

       

      So for example:

      the calculation below works

       

      Budget Bruto Loonomzet =
CALCULATE(
    [Budget],
    'Grootboekrekening'[CategoryDescription] IN {
        "Bruto XXXXXX"
    }
)

       

      This one works when the relation/dimension is not selected, but when the dimension is selected does not work

       

      Budget Bruto Loonomzet = 
CALCULATE(
	[Budget Bruto Loonomzet],
	FILTER(
		ALLSELECTED('Date'[Month]),
		ISONORAFTER('Date'[Month], MAX('Date'[Month]), DESC)
	)
)

       

       

       

      Best regards,
       

      • v-janeyg-msft's avatar
        v-janeyg-msft
        Icon for Community Support rankCommunity Support
        4 years ago

        Hi, Paco-MK2 

         

        Not sure if you're using calculated column or measure, I'm a bit confused.

        There is no such thing as calculated measure... 

         

        And Do you have only one data source? Is [Budget] a column or a measure and what is the code? What about [Budget Bruto Loonomzet]? A measure with the same name cannot appear in a table...

        You use calculate() in your code, and also use it nested, this may be a problem.

        You can try:

        VAR BudgetBrutoLoonomzet =
    CALCULATE (
        [Budget],
        'Grootboekrekening'[CategoryDescription] IN { "Bruto XXXXXX" }
    )
RETURN
    CALCULATE (
        [BudgetBrutoLoonomzet],
        FILTER (
            ALLSELECTED ( 'Date'[Month] ),
            ISONORAFTER ( 'Date'[Month], MAX ( 'Date'[Month] ), DESC )
        )
    )

        Your description is not clear, but I can see that there may be problems. If you can provide a sample, as well as your needs and desired results, I can help you.

         

        Best Regards,
        Community Support Team _ Janey
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.