Forum Discussion

Frumpy's avatar
Frumpy
Frequent Visitor
3 years ago

Measure imported from SSAS has different value in PowerBI

  • I have (part of) a table in SSMS that looks like this:

 

 

  • When summed and grouped by Year , the table looks like this:

 

  • I have imported the table(s) to SSAS and created the following measures for all three columns: 

 

DecisionsSelectedPeriod :=

VAR SVReportingPeriod= SELECTEDVALUE('Calendar'[TertalNo])

VAR DecisionsSelectedPeriod =

CALCULATE(

    SUM('fct Decisions'[Decisions]),

    ALL('Calendar'[ReportingPeriod]),

    'Calendar'[TertalNo] <= SVReportingPeriod

)

RETURN DecisionsSelectedPeriod

 

  • (The calendar table has a standard 1:Many relationship with the fact table)
  • The value of SUM('fct Decisions'[Decisions]) in SSAS is 15068, corresponding to the SUM of all decisions for all years in my grouped Table above. The same for Accepted (14630) and Declined (438), all values line up. So up till here it seems to be going fine.

 

  • Then, I deploy this to PowerBI server and put the measure in a card visual. Next to that, I have a slicer that selects 'Calendar'[Year] to filter the value per year. Now I expect to see the values corresponding to SUM of all decisions per year based on the slicer. Instead, I see the following values appear in my card visual when changing the Year slicer:

In other words, the amounts have been multiplied with factor 300-400

 

This exact same measure and slicer work absolutely fine in other tables, but not this one. Does anyone have any suggestions as to what I am doing wrong? If I need to provive more info, please let me know.

 

Thanks in advance!

Need Help

1 Reply

  • Bipin-Lala's avatar
    Bipin-Lala
    Icon for Solution Sage rankSolution Sage
    4 months ago

    I think you need to be more explicit about what you want to keep and what you want to kill in the filter context. Try rewriting the measure by including the Calendar table in the RemoveFilters function.

    This clears the entire calendar and prevents the Year slicer from clashing with your TertalNo logic.

     

    Let me know how it goes!