How can I calculate the result from different conditions

[Betty12111]

Dear Community,

Our environment is connected to the Analysis Services cube, so the numeric field cannot be created.

I have this table

transaction date	odd number	Product number	Sales Amount	Sales volume	division	group
2023/2/8	JBCPOC531515171	2.83E+12	5500	1	01	111
2023/2/8	JBCPOC531515145	2.82E+12	33744	6	00	113
2023/2/8	JBCPOC531511885	2.92E+12	877	1	02	114
2023/2/8	JBCPOC531511885	2.92E+12	1316	1	03	115
2023/2/8	JBCPOC531511885	2.92E+12	1681	1	03	115
2023/2/8	JBCPOC531511885	2.92E+12	1242	1	01	112
2023/2/8	JBCPOC531511885	2.99E+12	0	1	00	113
2023/2/8	JBCPOC531515114	2.83E+12	8075	1	03	116
2023/2/8	JBCPOC531515144	2.82E+12	3500	1	03	117
2023/2/8	JBCPOC531515144	2.82E+12	14000	4	01	111
2023/2/8	JBCPOC531515111	2.21E+12	3270	1	01	118
2023/2/8	JBCPOC531511967	2.82E+12	3500	1	01	118
2023/2/8	JBCPOC531511967	2.82E+12	17500	5	03	116
2023/2/8	JBCPOC531515155	2.82E+12	25200	20	00	113
2023/2/8	JBCPOC531515114	2.83E+12	5600	2	03	115
2023/2/8	JBCGV1111114397	2.83E+12	23275	7	03	119
2023/2/8	JBCGV1111114397	3.00E+12	0	1	03	119

How can I calculate the result from different conditions

Number of odd number 

interval	by division=01	by group=118
0-999	0	0
1000-1999	1	0
2000-2999	0	0
3000-3999	2	1
4000-4999	0	0
5000-5999	1	0
6000up	1	1

like

Is it possible to do this in BI Desktop?
Or I can only create fields in DW.

Any help would be greatly appreciated. 

[v-shex-msft · Accepted solution]

Hi @Betty12111,

Did you work with the live connection mode? If that is the case, you can't create column/table expression in power bi side.
It means you need to create the segment fields and table on your data source side. Then you can write measure expression to calculate based on current segmentation groups.

Dynamic segmentation – DAX Patterns

Regards,

Xiaoxin Sheng

[v-shex-msft · Accepted solution]

Hi @Betty12111,

Did you work with the live connection mode? If that is the case, you can't create column/table expression in power bi side.
It means you need to create the segment fields and table on your data source side. Then you can write measure expression to calculate based on current segmentation groups.

Dynamic segmentation – DAX Patterns

Regards,

Xiaoxin Sheng

[Betty12111]

hi @v-shex-msft , Can I create a dynamic table in the live connection mode?
I am using SQL Server Analysis Services(SSAS).
Can this be done in Cube?

[v-shex-msft · Accepted solution]

HI @Betty12111,

In fact, create the segment table on your data source side should be similar as create common tables. It can be designed with three fields: 'segment name', 'range start', 'range end'.

After you finish getting data to power bi side, you can create measure formula to lookup the segment name to get correspond ranges for calculation.(the measure result will dynamically change when you modify the table segment range settings on the data source side)

Regards,

Xiaoxin Sheng

[Betty12111]

I appreciate your help very much.😊

it worked successfully.