Forum Discussion

mb769's avatar
mb769
Icon for Advocate I rankAdvocate I
1 year ago
Solved

Use slicer to activate a calculation item

Struggling a lot to find an answer, and I'm afraid I'm making it unnecessarily complicated.

 

My final goal is to achieve something like this, where both the X- and Y-axis can be modified using slicers. Both of them can have more than 4 different values (and will grow in future development).

 

A bit of context: I count the number of students, and compare how they score in 2 different categories (e.g. the score for a certain subject, their number of abscenses, the amount of homework, etc.). These categories can be chosen on X- or Y-axis to look for possible interactions between the 2 categories (e.g. students who are more days absent from school, have a lower score for math).

These categories have to be measures, it is not possible to store them as calculated columns.

 

Since I do not want to create a different measure for each possible combination of 2 categories, I thought it was best to use a Calculation Group. There is a Calculation Item for each categorie (e.g. "number of abscences), and by using SELECTEDMEASURE() in each calculation item I can insert any other measure to calculate the number of students with each combination of categories.

 

My solution so far:

I create a Selector table to be used as a slicer. This selector determines both the chosen measure and the axis categories.

The data model for the X-axis looks like this:

SelectorX: the table I use in the slicer

Measure X-axis: Field parameter to chose the measure to show

Category X: Categories (labels) to be shown on the X-axis (as legend) - also a Field parameter

 

For the Y-axis, I started a similar approach, but the main difference is that the Measure for the Y-axis, is a Calculation Item. It is not possible to create a relation between SelectorY and the Calculation Group.

 

I thought to solve it by using a measure in my visual like this:

Measure =
CALCULATE (
   ?????,
   TREATAS (
      VALUES ( SelectorY[Selection] ),
      'Calculation Group'[Name]
   )
)

 

It does not work, mainly because: what measure should I put instead of ?????

I thought it could be MAX('Measure X-axis'[MeasureName]) but that does not work either: no data are shown in the visual.

 

What am I missing? Or am I overcomplicating things?

  • mb769's avatar
    mb769
    1 year ago

    I changed my approach to resolve the issue.
    Now I make a different measure for each possible combination of 2 catagories, and I use field parameters to switch between them.

9 Replies

  • Deku's avatar
    Deku
    Icon for Super User rankSuper User

    Why not use field parameters. Can have one each for x and y axis. 

  • Hi mb769 ,

     

    Not sure if I understand the question properly and why you created the field parameters and the calculation group.

     

    Is it possible to give a more clear example of what you want to achieve, if possible please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

    If the information is sensitive please share it trough private message.

  • I am not exactly clear with what you are tyring to achieve but you are using a calculation group as a dimension in  TREATAS? 

  • mb769's avatar
    mb769
    Icon for Advocate I rankAdvocate I

    Thanks for the replies so far.

    MFelix Here is (part of) my data: data file 
    Deku and aman_singh1310 : I am not using Field Parameters, because that would require me to create specific measures for each possible combination of categories (these data are not related)
    danextian : I read at different places that using TREATAS with the calculation group is a method of inserting a calculation item in a measure. I tried this method to be able to let the calculation group respond to the slicer. However, it is possible that this does not lead to a working solution.

    • MFelix's avatar
      MFelix
      Icon for Super User rankSuper User

      Hi mb769 ,

       

      What is the result you would get when you select the Agendapunten with the B-Codes for example?

      Is there a specific calculation you want to achieve or you just want to change the way the calculations are done?

      Can you give an example with two different charts assuming two different selections? I still don't understand what you are trying to achive because, on the visual you present different categories on the Y-Axis and on the legend but then the tables only get the values that show on the slicer not a list of other values.

    • v-saisrao-msft's avatar
      v-saisrao-msft
      Icon for Community Support rankCommunity Support

      Hi mb769,

       

      We haven’t heard back from you regarding your issue. If it has been resolved, please mark the helpful response as the solution and give a ‘Kudos’ to assist others. If you still need support, let us know.

       

      Thank you.

      • mb769's avatar
        mb769
        Icon for Advocate I rankAdvocate I

        I changed my approach to resolve the issue.
        Now I make a different measure for each possible combination of 2 catagories, and I use field parameters to switch between them.

  • v-saisrao-msft's avatar
    v-saisrao-msft
    Icon for Community Support rankCommunity Support

    Hi mb769,

    May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

    Thank you.