Grouping continuous measure into groups in DirectQuery

[Anonymous]

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 hope someone can help 🙂

[v-xiaotang]

Hi @Anonymous 

Could you please provide some sample data?

usually information below is needed

 (1) a sample file, you can replace raw data with bogus data to protect your privacy.

     or provide some sample data that fully covers your issue/question.

(2) give your expected result and its math formula based on the sample you provide.

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]

Hi Xiaotang,

I cannot provide data as it is running in DirectQuery.

But it is more of a conceptual question: Is it possible to group/segment values in DirectQuery?

For instance if a value in a measure is between 0 and 0,25 then the group should be '0 % - 25 %' and if the value is between 0,25 and 0,5 it should be grouped into '25 % - 50 %'. I've tried to create an example below.

Value	Group
0,10	0 % - 25 %
0,15	0 % - 25 %
0,21	0 % - 25 %
0,26	25 % - 50 %
0,37	25 % - 50 %

[v-xiaotang · Accepted solution]

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]

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]

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]

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]

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.

[Anonymous]

Hello,

Yes I did:

Where:

- Procentgruppe = Group

- 4_FEATURIZ_SOMMERHUSUDLEJNINGER = Sheet2$