Forum Discussion

jaryszek's avatar
jaryszek
Super User
7 months ago
Solved

Calculation group - how to make this working - example model attached.

I am trying to validate a Calculation Group in Power BI, but it does not seem to be applied to visuals.

Setup:

I created a Calculation Group called Granularity with two calculation items: Daily and Monthly.

I added a slicer using the calculation group column (Granularity[Name]).

Single select is enabled and I explicitly select “Daily”.

Test:
To verify the calc group is applied, I simplified the calculation item to:

Daily = 999

Expected behavior:
Any visual using a measure should display 999 when “Daily” is selected.

Actual behavior:
The visual continues to show the original measure result (or 0), not 999.
The Filters pane shows Granularity = (All) instead of Granularity = Daily.

Notes:

Visual uses an explicit measure (not implicit aggregation).

The calculation group is visible in the semantic model.

This happens consistently even with the calc item reduced to a constant value.

Dataset uses a hybrid model (some star schema facts, some satellite tables connected only to Dim_Date).

Question:
What conditions prevent a Calculation Group from being applied to a visual when the calculation item is selected via a slicer? Is there any limitation that would prevent using Calculation Groups to switch between different source measures based on a Granularity selection?

Example database column from MFelix from here:

https://community.fabric.microsoft.com/t5/Desktop/Trying-to-set-up-calculation-groups-in-order-to-show-daily-or/m-p/4874076/highlight/true#M1445747

Best,
Jacek

Need Help
  • MFelix's avatar
    MFelix
    7 months ago

    Hi jaryszek ,

     

    Ths is how calcaulation items work, no matter what calculation you have you will get the value on the calculation item in this case you have the following calculation items:

    Daily = 999

Monthly = 10000000

     

    Being a calculation Group whateve measure you apply this the result will be 999 or 10000000 so let's do this step by step.

     

    No calculation item applied:

     

    In this case the measure returns monthly or  Daily has you can see I have not selecteced any value from the Granularity slicer.

     

    Selecting Monthly Daily Value:

     

     

    When I select the monthly value since this is a measure the value of that measure is overwritten by 999

     

     

    When I select monthly it gets the 10M

     

    Basically you are overwritting the calculation and making it a new calculation.

     

    For the calculations group to work properly you can use the additional syntax:

     

    SELECTEDMEASURE

    ISSELECTEDMEASURE

    SELECTEDMEASUREFORMATSTRING

    SELECTEDMEASURENAME

     

    All of this allow you to tell on top of what the calculation is going to be performed if you just pickup a specific measure on your calculation group that value would always be used with that measure however using the syntax above you can do it for a specific calculation or for all of them.

     

    For example if my code was:

    Daily = IF(ISSELECTEDMEASURE([Date Hierarchy Selection]), SELECTEDMEASURE(), 999)

    For this measure Date Hierachy Selection it will always return the maximum value of the calculation group in this case Monthly:

     

     

    That is the reason why the previous calculation groups I have sent you were:

     

    Monthly = m = SELECTEDMEASURENAME()
RETURN
SWITCH(
    TRUE(),
    m = "Total Amortized Cost", [Total Amortized Cost (Monthly)],
    SELECTEDMEASURE()
)

    In this I check if the measure is "Total Amortized Cost", if it is I return the monthly calculation otherwise I return the value of the measure.

9 Replies

  • MFelix's avatar
    MFelix
    Super User
    7 months ago

    Hi jaryszek ,

     

    In the example you attach to the question of FBergamaschi , your slicer is based on the Date Hierarchy table not on your Granularity Calculation group.

    I have change the Calculations groups to the following:

    Monthly = 10000000

Daily =  999

     

    Has you can see when I use the slicer from the granularity is working has expected:

     

     

     

    Believe that the problem in this is the sync of the slicers and this was my error apologies for not giving you the full explanation about the sync slicers. When you sync slicers you have two properties: 

     

    • Sync field changes to other slicers - This will change the other slicers that are sync with the same fields so if you change one of the slicers for Date hierarchy the other will follow trough
    • Sync filter changes to others slicers - This only sincs the filter that you apply to selected slicers

    In this case you need to have only the second option active in both slicers and this will work properly when you have a slicer from each of the tables:

     

     

     

    Please find file attached and be aware that the calculation groups are with the examples so you need to changed them to the previous code that is comment on the calculation group itself.

     

    Once again I apologize for the miss leading you on the setup of the slicers.

    • jaryszek's avatar
      jaryszek
      Super User
      7 months ago

      One more question.

      How power bi knows that here It needs to trigger Calculation Group which is called "Granularity" in our case? 

      There is the measure within chart only, not calculation group...

      Best,
      Jacek

      • v-nmadadi-msft's avatar
        v-nmadadi-msft
        Community Support
        7 months ago

        Hi jaryszek ,

         

        The visual does not directly reference the calculation group, however, the filter context introduced by it causes the relevant date hierarchy to be applied during measure evaluation.

         

        I hope this information helps. Please do let us know if you have any further queries.
        Thank you