Forum Discussion

pr1351's avatar
pr1351
Frequent Visitor
2 years ago

How to create measure to aggregate rows

Hi,

I have this power BI dashboard set as below and the table may change at different month

 

Iwant to create a measure that look if 'Queensland -outback' is less than 10 then change the 'Darling Downs - Maranoa' to 'Darling Downs - Maranoa & Queensland -outback' and agregate these two raws together then remove 'Queensland -outback'. Thanks 

Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    2 years ago

    pr1351 , Try a measure like

    if( calculate([Measure], Table[Statistical Area] = "Queensland -outback") <10 ,

    Switch(True() ,

    max(Table[Statistical Area] ) = "Darling Downs - Maranoa", calculate([Measure], Table[Statistical Area] in { "Darling Downs - Maranoa" , "Queensland -outback" } ) ,

    max(Table[Statistical Area] ) = "'Queensland -outback", blank(), [Measure]),
    [Measure])))

     

    • pr1351's avatar
      pr1351
      Frequent Visitor
      2 years ago

      Thanks very much. I can not use this formula within the Power BI desktop version I am using it is Version 2.126.1261 and it is not under Microsoft fabric . 

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi pr1351 

         

        It doesn't matter which version of Power BI Desktop you are using. Usually a DAX formula can be used in any version unless that the formula contains some new DAX functions which are not supported in an old version. 

         

        One problem here is that the "Statistical Area" in your table visual is a column from the data table. Although we can create a measure to replace the [Sum of Payments] in the table visual, we cannot modify the column value from 'Darling Downs - Maranoa' to 'Darling Downs - Maranoa & Queensland -outback'. In addition, when the rest three fields (Sum of Value, Sum of Application, Sum of Value_1) are in the same table visual and their values are not blank for 'Darling Downs - Maranoa', we cannot hide this row if you want their values to be displayed. 

         

        Perhaps you need to adjust the desired result then we can consider if possible to achieve it. 

         

        Best Regards,
        Jing