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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

how to create a measure in DAX conditioned to a category of another field

Hello community,

 

First of all, I am not the best developer in the world, as I don't have that background, but I am having a hard time finding a solution to the following problem:

 

I have a table like this: 

ItemABC
Pear1043
Apple525
Orange314
Pear863
Orange216
Apple429
Pear751

 

and I need to create 1 measure that I can bring into a table or matrix, knowing that each category it has different measures, one for every item like this:

Total Apple = SUM(A) + SUM(B)

Total Orange = SUM(B) + SUM(C)

Total Pear = SUM(A) + SUM(C)

 

I was successful doing this by adding a custom column at Power Query, but I am wondering if I can develop a DAX measure that can do the same thing. 

 

After some research, I was able to do it combining IF(VALUES(...), however, it returns a table, so I can't insert the measure in a table or matrix when no filter is selected. I also tried using variables 'VAR', but was not successful -I should be doing something wrong-.

 

I also know that this measure requires line-by-line evaluation, but I am wondering if is any DAX formula or expression that I can use.

 

Thank you very much in advance

Pablo

1 ACCEPTED SOLUTION
v-danhe-msft
Employee
Employee

Hi @Anonymous ,

Based on my test, you could refer to below measure:

Measure = var a=CALCULATE(SUM(Table1[A]),ALL(Table1))+CALCULATE(SUM(Table1[B]),ALL(Table1))
        var b=CALCULATE(SUM(Table1[B]),ALL(Table1))+CALCULATE(SUM(Table1[C]),ALL(Table1))
        var c=CALCULATE(SUM(Table1[A]),ALL(Table1))+CALCULATE(SUM(Table1[C]),ALL(Table1))
        return IF(CALCULATE(MAX('Table1'[Item]))="Apple",a,
                IF(CALCULATE(MAX('Table1'[Item]))="Orange",b,c))

Result:

1.PNG

You could also download the pbix file to have a view.

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then 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-danhe-msft
Employee
Employee

Hi @Anonymous ,

Based on my test, you could refer to below measure:

Measure = var a=CALCULATE(SUM(Table1[A]),ALL(Table1))+CALCULATE(SUM(Table1[B]),ALL(Table1))
        var b=CALCULATE(SUM(Table1[B]),ALL(Table1))+CALCULATE(SUM(Table1[C]),ALL(Table1))
        var c=CALCULATE(SUM(Table1[A]),ALL(Table1))+CALCULATE(SUM(Table1[C]),ALL(Table1))
        return IF(CALCULATE(MAX('Table1'[Item]))="Apple",a,
                IF(CALCULATE(MAX('Table1'[Item]))="Orange",b,c))

Result:

1.PNG

You could also download the pbix file to have a view.

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hello Daniel He,

 

Thank you so much for the response, it works perfectly. 

I was struggling with the filters, but your solution works perfectly fine.

Another great lesson in DAX

 

tarunsingla
Solution Sage
Solution Sage

Use Calculate in your calculated measure to sum values from columns, applying a filter on the item.

 

e.g. Total Apple = CALCULATE(SUM(A), Item = "Apple") + CALCULATE(SUM(B), Item = "Apple")

 

Reference: https://docs.microsoft.com/en-us/dax/calculate-function-dax

 

Regards,

Tarun

Anonymous
Not applicable

Hello Tarun,

 

thank you for the answer, but I already did it and it is not what I am looking to do.

 

I want to create just 1 measure that I can bring to a table or matrix and populate each totals withing the same column, not creating 3 different measures with filters.

Sorry if I wasn't very clear earlier

 

Thank you very much

Pablo

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.