cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
franorio Helper III
Helper III

Measure for different measures?

Hello everybody,

got 5 different measures in 5 different tables. 

Table1, measure A

Table 2, measure B  

Table 3, measure C

Table 4, measure D

Table 5, measure E

 

all measures return a value in %

 

is there a way to group all those measure under one, without any math opperation, I don't want them to sum, divide, or anything. 

 

would be awesome a IF formula with color:

 

NewMeasure = If( measure A & measure B & measure C & measure D & measure E > 0.9, ¨green¨

                                                                                                                                       > 0.8 < 0.9 ¨yellow¨

                                                                                                                                       < 0.79 ¨red¨ )

 

is this possible?

other possibility would be to agroup them all under a unique measure and drag this new messure under color saturation field, how should I agroup them?

 

Thanks & Regards!

 

 

6 REPLIES 6
Highlighted
Microsoft Phil_Seamark
Microsoft

Re: Measure for different measures?

Give this a crack

 

Measure = 
VAR MyTotal = [MeasureA] + [MeasureB] + [MeasureC] + [MeasureD] + [MeasureE]
RETURN SWITCH(
                TRUE() ,
                MyTotal > .9 , "Green" ,
                MyTotal > .8 , "Orange" ,
                "Red")

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

franorio Helper III
Helper III

Re: Measure for different measures?

Thanks Phil!
The formula works and  returns ¨Green¨, ¨Yellow¨ and ¨Red¨. But it does not work for me, it calculates the var for the sum of all measures.
I need to consider all of them in the same formula, but to return the color for each measure by it self if it applys, for example measure C if 90% = green and if measure b 82% = yellow  & also if measure B = .....

by the moment it works as total of all.. var = measure a + measure b + measure c + measure d = 90% = green

is there a way to separe the total of each measure by it self?

 

Thanks a lot

 

Regards

franorio Helper III
Helper III

Re: Measure for different measures?

@Phil_Seamarkhow are you? Sorry to bother you, but I remembered you had helped me before, and you are kind a measure's Guru.. Could I please ask you some help with the following issue I'm having.

 

This is how my data looks like:

data1.PNG

 

 

 

It's reletaed to a movements report, to have track of how many promotions, lateral movements or terminations were on the month and if they are adhered to the companies policy. So what I need to do is count how many OK status are for each previous area (Área Anterior) column. I'm doing this with the following formula:

 

Mov Adherence = VAR selectedTable = CALCULATETABLE ( 'MovementsReport' ) RETURN CALCULATE ( COUNTROWS ( 'MovementsReport' ), FILTER ( 'MovementsReport', 'MovementsReport'[Status] = "OK" ) )

 

As a company policy, if there is a lateral movement to another and different area, it should impact on both, previous area (Área Anterior) and the new Area (Area). 

 

So this is my formula to count SUMAR CF a column that is the column that shows my if it should be added to the new area.

 

COUNT of sumar cf = CALCULATE(
COUNTROWS('MovementsReport') ,
FILTER(
'MovementsReport',
NOT ISBLANK('MovementsReport'[Sumar CF a])
)
)

 

 

The problem is my relationship between my DimArea Table (the one I'm using as rows on a matrix) is created to Previous Area column..

 

relaciones.PNG

 

so this last formula applied to Sumar CF a, impacting in the previous Area (Marketing and Sales) like this, instead of impacting on the new Areas, SALES and PEOPLE

 

matriz totales.PNG

 

 

Summarizing... what I need is a formula to count values on Sumar CF a column, and to impact on Area, not Área Anterior column.

Is there a way to do this with a measure?

 

Maybe with a CONTAINS formula for Sumar CF a column, if Status para Sumar CF a is OK and EP Scope is OK, add to Area column??

 

regards!!!

 

Microsoft Phil_Seamark
Microsoft

Re: Measure for different measures?

Hi @franorio

 

You can create additional relationshships between DimAreas and MovementsReport, only they will be Inactive (or dotted lines).

 

What this means is measures will use the Active relationship by default, but you can use the USERELATIONSHIP function in your DAX meaures when you want to specify an alternative relationship.  

 

Does that make sence?

 

inactive.png

 

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

franorio Helper III
Helper III

Re: Measure for different measures?

Many thanks @Phil_Seamark!

 

I created the dotted relationship between my Column Area of my DIMAreas and he column Area of my MovementsReport Table,. but i'm not sure how the forumla should look like..

 

Count of sumar cf = CALCULATE
COUNTROWS('MovementsReport')
FILTER(
'MovementsReport',
NOT ISBLANK('MovementsReport'[Sumar CF a]), USERELATIONSHIP(DimAreas[Área],MovementsReport[Area])
)
)

 

Tried this out, but it's not working

asrivastava
New Member

Re: Measure for different measures?

NewMeasure = IF(AND(AND(AND(measure A>.9, measure B>.9), AND(measure C>.9, measure D>.9)), measure E > 0.9), ¨green¨,
                          IF(AND(AND(AND(measure A<.79, measure B<.79), AND(measure C<.79, measure D<.79)), measure E < 0.79), ¨red¨, ¨yellow¨))

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors