Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Grouping continuous measure into groups in DirectQuery

Hi,   I have the following below which I want to make group based on % so: What I have: What I'm looking for:   NOTE: This has to be implemented in a DirectQuery context!   I ...
Need Help
  • v-xiaotang's avatar
    v-xiaotang
    4 years ago

    Hi Anonymous 

    I'll test it later under DQ mode, before that, do you have dimension table containing groups?

    If not, you need to use switch() to get the expected outcome.

    -

    Update:

    create the measure below

    group = 
var _value=[Measure]
return SWITCH(TRUE(),
_value>0&&_value<=0.25,"0 % - 25 %",
_value>0.25 && _value<=0.5,"25 % - 50 %")

     

    Best Regards,

    Community Support Team _Tang

    If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

v-xiaotang's avatar
v-xiaotang
Community Support
4 years ago

Hi Anonymous 

I'll test it later under DQ mode, before that, do you have dimension table containing groups?

If not, you need to use switch() to get the expected outcome.

-

Update:

create the measure below

group = 
var _value=[Measure]
return SWITCH(TRUE(),
_value>0&&_value<=0.25,"0 % - 25 %",
_value>0.25 && _value<=0.5,"25 % - 50 %")

 

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

I know I have set it to solved but I can see that it does not help my problem unfortunately.

This is my data below with the measure. The problem is that I cannot count the number of products within each group:

 

If I count:

 

 

I want to count the number of products within the different percentage-groups.

  • v-xiaotang's avatar
    v-xiaotang
    Community Support
    4 years ago

    Hi  Anonymous 

    If you want to count each group, you need to have a dimension table containing groups.

    Then create the measure

    count = CALCULATE(COUNTROWS('Sheet2$'),FILTER('Sheet2$','Sheet2$'[group]=MIN('Group'[group])))

     

     

    Best Regards,

    Community Support Team _Tang

    If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      It says it cannot find the table 'Group' when I make the measure. The 'Sheet2$' is not connected to 'Group' - I guess that is the problem? I cannot connect them as the group in the 'Group' is a calculated measure.

       

      • v-xiaotang's avatar
        v-xiaotang
        Community Support
        4 years ago

        Hi Anonymous 

        Did you create the table Group? If not, you need to create it first.  'Sheet2$' does not need to connect to 'Group'

         

        Best Regards,

        Community Support Team _Tang

        If this post helps, please consider Accept it as the solution to help the other members find it more quickly.