cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
franorio Member
Member

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

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

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

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 Occasional Visitor
Occasional Visitor

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
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors