Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Lopez0090
Helper III
Helper III

Output the result of group by without decreasing the number of lines.

What we want to achieve is shown in the image.

If the Code is "AAA", the "Value" is aggregated with the same Num2 (e.g. B731 or B867).

image1.png

For example... Code is AAA and Num2 is B731.
Code is AAA and Num2 is B731 has two Values ("100" and "-100").
When the two "Values" are aggregated, the result is 100+(-100), so the result is 0.
We want to output this "0" result to the two rows of B731.


If Code is not "AAA", the data is totaled by "Num1".

 

In summary, I think it goes like this.

  • If Code = AAA, then Aggregate by Num2
  • If Code <> AAA, then Aggregate by Num1
  • The number of rows should remain the same

 

Is it possible to achieve this with a major? Or is there some way to have it calculated in a computed column?

I tried to group them in the Power Query Editor, but It did not work.

 

If anyone has any good ideas or methods, please let me know.

 

Best regards, Lopez

1 ACCEPTED SOLUTION
v-xiaotang
Community Support
Community Support

Hi @Lopez0090 

Thanks for reaching out to us.

>>

In summary, I think it goes like this.

  • If Code = AAA, then Aggregate by Num2
  • If Code <> AAA, then Aggregate by Num1
  • The number of rows should remain the same

You can try this,

Column =
IF (
    'Table'[Code] = "AAA",
    SUMX (
        FILTER ( ALL ( 'Table' ), 'Table'[Num2] = EARLIER ( 'Table'[Num2] ) ),
        [Value]
    ),
    SUMX (
        FILTER ( ALL ( 'Table' ), 'Table'[Num1] = EARLIER ( 'Table'[Num1] ) ),
        [Value]
    )
)

 

vxiaotang_0-1658124668516.png

 

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.

View solution in original post

4 REPLIES 4
v-xiaotang
Community Support
Community Support

Hi @Lopez0090 

Thanks for reaching out to us.

>>

In summary, I think it goes like this.

  • If Code = AAA, then Aggregate by Num2
  • If Code <> AAA, then Aggregate by Num1
  • The number of rows should remain the same

You can try this,

Column =
IF (
    'Table'[Code] = "AAA",
    SUMX (
        FILTER ( ALL ( 'Table' ), 'Table'[Num2] = EARLIER ( 'Table'[Num2] ) ),
        [Value]
    ),
    SUMX (
        FILTER ( ALL ( 'Table' ), 'Table'[Num1] = EARLIER ( 'Table'[Num1] ) ),
        [Value]
    )
)

 

vxiaotang_0-1658124668516.png

 

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 

Thank you very much.
Actually, I need to use a dynamically calculated Value (measure) at [Value], and I would like to express this expression in a measure, is it possible to create it in a measure?

Hi @Lopez0090 

Thanks for your reply.

You can try this

measure=
IF (
    min('Table'[Code] )= "AAA",
    SUMX (
        FILTER ( ALL ( 'Table' ), 'Table'[Num2] = min( 'Table'[Num2] ) ),
        [Value]
    ),
    SUMX (
        FILTER ( ALL ( 'Table' ), 'Table'[Num1] = min( 'Table'[Num1] ) ),
        [Value]
    )
)

 

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.

Lopez0090
Helper III
Helper III

My English is not very good, so please let me know if I am not very clear.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.