Measure for different measures?

[franorio]

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!

[asrivastava]

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¨))

[Phil_Seamark]

Give this a crack

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

[franorio]

@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:

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..

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

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!!!

[Phil_Seamark]

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?

[franorio]

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

[franorio]

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