Skip to content
TheChatArchiveThe Chat Archive
gpt-3.5-turbo-0613· OpenAIAi2 WildChat-4.8M (AI2)

Write DAX code to determine quadrant to which belongs a point

  1. User

    Write DAX code to determine quadrant to which belongs a point

  2. Assistant

    To determine the quadrant to which a point belongs in DAX (Data Analysis Expressions), you can use the following code:

    Quadrant = 
    VAR X = [X Coordinate]
    VAR Y = [Y Coordinate]
    RETURN
        SWITCH(TRUE(),
            X > 0 && Y > 0, "Quadrant 1",
            X < 0 && Y > 0, "Quadrant 2",
            X < 0 && Y < 0, "Quadrant 3",
            X > 0 && Y < 0, "Quadrant 4",
            BLANK()
        )
    

    In this code, replace [X Coordinate] and [Y Coordinate] with the respective columns or measures that contain the X and Y coordinates of the point.

    The SWITCH function evaluates the conditions sequentially until it finds a true condition. If none of the conditions are true, it returns a blank value. In this case, we are checking the signs of the X and Y coordinates to determine the quadrant. If both X and Y are positive, the point is in Quadrant 1. If X is negative and Y is positive, the point is in Quadrant 2, and so on.

    You can add this code as a calculated column to your data table or use it within a measure depending on your specific requirements.

  3. User

    Write DAX code to determine quadrant to which belongs a point if lines that determine quadrants are measures

  4. Assistant

    If the lines that determine the quadrants are measures (e.g., equations of lines or slopes), you can use the following DAX code:

    Quadrant = 
    VAR X = [X Coordinate]
    VAR Y = [Y Coordinate]
    VAR Quadrant1Line = [Quadrant 1 Line]
    VAR Quadrant2Line = [Quadrant 2 Line]
    VAR Quadrant3Line = [Quadrant 3 Line]
    VAR Quadrant4Line = [Quadrant 4 Line]
    RETURN
        SWITCH(TRUE(),
            X > 0 && Y > 0 && Y > X * Quadrant1Line, "Quadrant 1",
            X < 0 && Y > 0 && Y > X * Quadrant2Line, "Quadrant 2",
            X < 0 && Y < 0 && Y < X * Quadrant3Line, "Quadrant 3",
            X > 0 && Y < 0 && Y < X * Quadrant4Line, "Quadrant 4",
            BLANK()
        )
    

    In this code, replace [X Coordinate] and [Y Coordinate] with the respective columns or measures that contain the X and Y coordinates of the point. Replace [Quadrant 1 Line], [Quadrant 2 Line], [Quadrant 3 Line], and [Quadrant 4 Line] with the measures that represent the equations or slopes of the lines that determine the quadrants.

    The SWITCH function checks the conditions one by one and returns the corresponding quadrant if the condition is true. The conditions here include checking if X and Y are positive or negative and comparing them to the lines that determine the quadrants.

    Again, you can add this code as a calculated column or use it within a measure based on your specific needs.

  5. User

    Fix my code in DAX

    Qtn = SWITCH ( TRUE(), AND([ACT Total Reg Disc+SB, % TGS] > [AVG_OX],[vs LY (GS), %] > [AVG_OY]), 1, AND([ACT Total Reg Disc+SB, % TGS] > [AVG_OX],[vs LY (GS), %] < [AVG_OY]), 2, AND([ACT Total Reg Disc+SB, % TGS] < [AVG_OX],[vs LY (GS), %] < [AVG_OY]), 3, AND([ACT Total Reg Disc+SB, % TGS] < [AVG_OX],[vs LY (GS), %] > [AVG_OY]), 4)

    AVG_OX = AVERAGEX(SUMMARIZE(Payers, Payers[Сеть], "toAverage", [ACT Total Reg Disc+SB, % TGS]), [ACT Total Reg Disc+SB, % TGS]) AVG_OY = AVERAGEX(SUMMARIZE(Payers, Payers[Сеть], "toAverage", [vs LY (GS), %]), [vs LY (GS), %])

    [ACT Total Reg Disc+SB, % TGS] and [vs LY (GS), %] are measures

  6. Assistant

    The code you provided has a mix of Power BI formulas and Power Pivot formulas. To fix your code, you need to make sure you are using only DAX functions that are compatible with Power BI.

    Assuming you are working in Power BI, here is the corrected code:

    Qtn = 
    SWITCH (
        TRUE(),
        AND([ACT Total Reg Disc+SB, % TGS] > [AVG_OX], [vs LY (GS), %] > [AVG_OY]), 1,
        AND([ACT Total Reg Disc+SB, % TGS] > [AVG_OX], [vs LY (GS), %] < [AVG_OY]), 2,
        AND([ACT Total Reg Disc+SB, % TGS] < [AVG_OX], [vs LY (GS), %] < [AVG_OY]), 3,
        AND([ACT Total Reg Disc+SB, % TGS] < [AVG_OX], [vs LY (GS), %] > [AVG_OY]), 4,
        BLANK()
    ) 
    
    AVG_OX = 
    AVERAGEX(
        SUMMARIZE(Payers, Payers[Сеть], "toAverage", [ACT Total Reg Disc+SB, % TGS]), 
        [toAverage]
    )
    
    AVG_OY = 
    AVERAGEX(
        SUMMARIZE(Payers, Payers[Сеть], "toAverage", [vs LY (GS), %]), 
        [toAverage]
    )
    

    Make sure to replace [ACT Total Reg Disc+SB, % TGS] and [vs LY (GS), %] with the actual measures you have in your data model.

    In the AVG_OX and AVG_OY measures, I used SUMMARIZE to create a table with unique values of the Payers[Сеть] column and a column named "toAverage" based on the respective measures. Then, I used AVERAGEX to calculate the averages of the "toAverage" column.

    By fixing these issues, the code should work properly in Power BI.

  7. User

    Fix my code in DAX in order AVG_OX and AVG_OY to be fixed

    Qtn = SWITCH ( TRUE(), AND([ACT Total Reg Disc+SB, % TGS] > [AVG_OX], [vs LY (GS), %] > [AVG_OY]), 1, AND([ACT Total Reg Disc+SB, % TGS] > [AVG_OX], [vs LY (GS), %] < [AVG_OY]), 2, AND([ACT Total Reg Disc+SB, % TGS] < [AVG_OX], [vs LY (GS), %] < [AVG_OY]), 3, AND([ACT Total Reg Disc+SB, % TGS] < [AVG_OX], [vs LY (GS), %] > [AVG_OY]), 4, BLANK() )

    AVG_OX = AVERAGEX( SUMMARIZE(Payers, Payers[Сеть], “toAverage”, [ACT Total Reg Disc+SB, % TGS]), [toAverage] )

    AVG_OY = AVERAGEX( SUMMARIZE(Payers, Payers[Сеть], “toAverage”, [vs LY (GS), %]), [toAverage] )

  8. Assistant

    To fix your code in DAX and ensure that AVG_OX and AVG_OY are treated as fixed values, you can use the following modified code:

    Qtn = 
    SWITCH (
        TRUE(),
        AND([ACT Total Reg Disc+SB, % TGS] > CALCULATE(AVG([ACT Total Reg Disc+SB, % TGS]), ALL(Payers)), [vs LY (GS), %] > CALCULATE(AVG([vs LY (GS), %]), ALL(Payers))), 1,
        AND([ACT Total Reg Disc+SB, % TGS] > CALCULATE(AVG([ACT Total Reg Disc+SB, % TGS]), ALL(Payers)), [vs LY (GS), %] < CALCULATE(AVG([vs LY (GS), %]), ALL(Payers))), 2,
        AND([ACT Total Reg Disc+SB, % TGS] < CALCULATE(AVG([ACT Total Reg Disc+SB, % TGS]), ALL(Payers)), [vs LY (GS), %] < CALCULATE(AVG([vs LY (GS), %]), ALL(Payers))), 3,
        AND([ACT Total Reg Disc+SB, % TGS] < CALCULATE(AVG([ACT Total Reg Disc+SB, % TGS]), ALL(Payers)), [vs LY (GS), %] > CALCULATE(AVG([vs LY (GS), %]), ALL(Payers))), 4,
        BLANK()
    ) 
    
    AVG_OX = AVERAGE(Payers[ACT Total Reg Disc+SB, % TGS])
    
    AVG_OY = AVERAGE(Payers[vs LY (GS), %])
    

    In this updated code:

    • The CALCULATE function is used to calculate the average of [ACT Total Reg Disc+SB, % TGS] and [vs LY (GS), %] across all Payers, regardless of any filters on Payers.
    • The ALL function is used to remove any filters from the Payers table, allowing the CALCULATE function to consider all the values.
    • The AVERAGE function is used instead of AVERAGEX to calculate the average for the fixed columns [ACT Total Reg Disc+SB, % TGS] and [vs LY (GS), %] directly.

    Note that you should replace [ACT Total Reg Disc+SB, % TGS] and [vs LY (GS), %] with the actual columns in your Payers table that represent the respective measures.

    With these modifications, AVG_OX and AVG_OY will be considered as fixed values, and the Qtn measure will compare the values of [ACT Total Reg Disc+SB, % TGS] and [vs LY (GS), %] against these fixed values to determine the quadrants correctly.

  9. User

    Fix my code in DAX in order AVG_OX and AVG_OY to be fixed

    Qtn = SWITCH ( TRUE(), AND([ACT Total Reg Disc+SB, % TGS] > [AVG_OX], [vs LY (GS), %] > [AVG_OY]), 1, AND([ACT Total Reg Disc+SB, % TGS] > [AVG_OX], [vs LY (GS), %] < [AVG_OY]), 2, AND([ACT Total Reg Disc+SB, % TGS] < [AVG_OX], [vs LY (GS), %] < [AVG_OY]), 3, AND([ACT Total Reg Disc+SB, % TGS] < [AVG_OX], [vs LY (GS), %] > [AVG_OY]), 4, BLANK() )

    AVG_OX = AVERAGEX( SUMMARIZE(Payers, Payers[Сеть], “toAverage”, [ACT Total Reg Disc+SB, % TGS]), [toAverage] )

    AVG_OY = AVERAGEX( SUMMARIZE(Payers, Payers[Сеть], “toAverage”, [vs LY (GS), %]), [toAverage] )

    [ACT Total Reg Disc+SB, % TGS] and [vs LY (GS), %] are measures

  10. Assistant

    To fix your code in DAX and ensure that AVG_OX and AVG_OY are treated as fixed values, you can use the following modified code:

    Qtn = 
    SWITCH (
        TRUE(),
        AND([ACT Total Reg Disc+SB, % TGS] > CALCULATE([AVG_OX], ALL(Payers)), [vs LY (GS), %] > CALCULATE([AVG_OY], ALL(Payers))), 1,
        AND([ACT Total Reg Disc+SB, % TGS] > CALCULATE([AVG_OX], ALL(Payers)), [vs LY (GS), %] < CALCULATE([AVG_OY], ALL(Payers))), 2,
        AND([ACT Total Reg Disc+SB, % TGS] < CALCULATE([AVG_OX], ALL(Payers)), [vs LY (GS), %] < CALCULATE([AVG_OY], ALL(Payers))), 3,
        AND([ACT Total Reg Disc+SB, % TGS] < CALCULATE([AVG_OX], ALL(Payers)), [vs LY (GS), %] > CALCULATE([AVG_OY], ALL(Payers))), 4,
        BLANK()
    ) 
    
    AVG_OX = [ACT Total Reg Disc+SB, % TGS]
    
    AVG_OY = [vs LY (GS), %]
    

    In this updated code:

    • The CALCULATE function is used to calculate the measure values [AVG_OX] and [AVG_OY] across all Payers, regardless of any filters on Payers.
    • The ALL function is used to remove any filters from the Payers table, allowing the CALCULATE function to consider all the values.
    • [ACT Total Reg Disc+SB, % TGS] and [vs LY (GS), %] are already measures, so you can directly use them in AVG_OX and AVG_OY without the need for AVERAGEX or SUMMARIZE.

    Note that the [ACT Total Reg Disc+SB, % TGS] and [vs LY (GS), %] measures should already be defined correctly in your data model.

    With these modifications, AVG_OX and AVG_OY will be considered as fixed values, and the Qtn measure will compare the values of [ACT Total Reg Disc+SB, % TGS] and [vs LY (GS), %] against these fixed values to determine the quadrants correctly.

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.