Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Multiple IF functions

Hi,   I'm very new to DAX functions and have been trying to combine multiple IF criterias, with 2 different data sources. The end result i'm looking for is pipeline data divided by budget by quarte...
  • v-haibl-msft's avatar
    v-haibl-msft
    9 years ago

    Anonymous

     

    I just test with the two tables you provided, they are related with Region. Since you want Q2 to calculate on qualified pipeline, I create a new qualified column which only keeps the actual qualified status for Q2, and other quarters are updated to Y.

     

    Qualified_Update =
IF ( SEARCH ( "Q2", Table1[Fiscal Period],, -1 ) > 0, Table1[Qualified], "Y" )

    We also need to create a year quarter column.

    YearQuarter = 
RIGHT ( Table1[Fiscal Period], 4 ) * 100
    + MID ( Table1[Fiscal Period], 2, 1 )

    Then create three measures to get the results shown in excel.

    Pipeline_2 = 
VAR YearQuarter =
    CALCULATE ( AVERAGE ( Table1[YearQuarter] ) )
RETURN
    IF (
        CONTAINS ( Table1, Table1[YearQuarter], YearQuarter ),
        CALCULATE (
            SUM ( Table1[USD] ),
            FILTER (
                ALLSELECTED ( Table1 ),
                Table1[YearQuarter] = YearQuarter
                    && Table1[Qualified_Update] = "Y"
            )
        ),
        CALCULATE (
            SUM ( Table1[USD] ),
            FILTER ( ALLSELECTED ( Table1 ), Table1[Qualified_Update] = "Y" )
        )
    )
    Budget_2 = 
VAR YearQuarter =
    CALCULATE ( AVERAGE ( Table1[YearQuarter] ) )
VAR Q1Budget =
    CALCULATE ( SUM ( Table2[Q1 Budget] ), ALLSELECTED ( Table1 ) )
VAR Q2Budget =
    CALCULATE ( SUM ( Table2[Q2 Budget] ), ALLSELECTED ( Table1 ) )
VAR Q3Budget =
    CALCULATE ( SUM ( Table2[Q3 Budget] ), ALLSELECTED ( Table1 ) )
VAR Q4Budget =
    CALCULATE ( SUM ( Table2[Q4 Budget] ), ALLSELECTED ( Table1 ) )
RETURN
    SWITCH (
        YearQuarter,
        201701, Q1Budget,
        201702, Q2Budget,
        201703, Q3Budget,
        201704, Q4Budget,
        Q1Budget + Q2Budget
            + Q3Budget
            + Q4Budget
    )
    Coverage_2 = 
ROUND ( [Pipeline_2] / [Budget_2], 1 )
& "x"

     

    Best Regards,

    Herbert

Greg_Deckler's avatar
Greg_Deckler
Community Champion
9 years ago

Is this a column or a measure? Are the tables related? Can you post some sample data to recreate your data model and test?

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    hi Greg_Deckler,
     
    Pipeline - Table
    Pipeline[Fiscal Period] - column within above table
    Q2 Qualified Pipeline - Measure
     
    Yes, tables are related based on the Region, product line and pipeline opportunity owner (owner info not included in sample data).
     
    I have attached sample data with two tables (pipeline and budget data) and also the final view that I am looking for.
     
    Hope this helps resolve my query. Thanks!

     

    PS - I have the sample data in an excel, how do i attach it here. There does;t seem to be any option except to embed a picture :-(

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Hi Greg_Deckler

       

      Attaching image!

       

      • v-haibl-msft's avatar
        v-haibl-msft
        Microsoft Employee
        9 years ago

        Anonymous

         

        I just test with the two tables you provided, they are related with Region. Since you want Q2 to calculate on qualified pipeline, I create a new qualified column which only keeps the actual qualified status for Q2, and other quarters are updated to Y.

         

        Qualified_Update =
IF ( SEARCH ( "Q2", Table1[Fiscal Period],, -1 ) > 0, Table1[Qualified], "Y" )

        We also need to create a year quarter column.

        YearQuarter = 
RIGHT ( Table1[Fiscal Period], 4 ) * 100
    + MID ( Table1[Fiscal Period], 2, 1 )

        Then create three measures to get the results shown in excel.

        Pipeline_2 = 
VAR YearQuarter =
    CALCULATE ( AVERAGE ( Table1[YearQuarter] ) )
RETURN
    IF (
        CONTAINS ( Table1, Table1[YearQuarter], YearQuarter ),
        CALCULATE (
            SUM ( Table1[USD] ),
            FILTER (
                ALLSELECTED ( Table1 ),
                Table1[YearQuarter] = YearQuarter
                    && Table1[Qualified_Update] = "Y"
            )
        ),
        CALCULATE (
            SUM ( Table1[USD] ),
            FILTER ( ALLSELECTED ( Table1 ), Table1[Qualified_Update] = "Y" )
        )
    )
        Budget_2 = 
VAR YearQuarter =
    CALCULATE ( AVERAGE ( Table1[YearQuarter] ) )
VAR Q1Budget =
    CALCULATE ( SUM ( Table2[Q1 Budget] ), ALLSELECTED ( Table1 ) )
VAR Q2Budget =
    CALCULATE ( SUM ( Table2[Q2 Budget] ), ALLSELECTED ( Table1 ) )
VAR Q3Budget =
    CALCULATE ( SUM ( Table2[Q3 Budget] ), ALLSELECTED ( Table1 ) )
VAR Q4Budget =
    CALCULATE ( SUM ( Table2[Q4 Budget] ), ALLSELECTED ( Table1 ) )
RETURN
    SWITCH (
        YearQuarter,
        201701, Q1Budget,
        201702, Q2Budget,
        201703, Q3Budget,
        201704, Q4Budget,
        Q1Budget + Q2Budget
            + Q3Budget
            + Q4Budget
    )
        Coverage_2 = 
ROUND ( [Pipeline_2] / [Budget_2], 1 )
& "x"

         

        Best Regards,

        Herbert