Forum Discussion

franorio's avatar
franorio
Helper III
9 years ago

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...
Need Help
Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
9 years ago

Give this a crack

 

Measure = 
VAR MyTotal = [MeasureA] + [MeasureB] + [MeasureC] + [MeasureD] + [MeasureE]
RETURN SWITCH(
                TRUE() ,
                MyTotal > .9 , "Green" ,
                MyTotal > .8 , "Orange" ,
                "Red")
  • franorio's avatar
    franorio
    Helper III
    9 years ago

    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's avatar
    franorio
    Helper III
    9 years ago

    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's avatar
      Phil_Seamark
      Microsoft Employee
      9 years ago

      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's avatar
        franorio
        Helper III
        9 years ago

        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