Significance Formula

Hi

I’ve created a table to evaluate results of a test.

I’m struggling to come up with a formula to determine whether the test is significant or not.

In the example below the test is significant because Significance Low for ‘B&W No Pics’ is higher than Significance High for ‘Colour & Pics’.

I would like the card to say ‘Yes’ or ‘No’ depending on whether there is significance. The card below says Yes because I typed it in. I would prefer a formula.

Thanks

@KieftyKids,

Give this a go…

  Sig Test = 

VAR BWLow =
CALCULATE(
    SELECTEDVALUE( Data[ Significance Low] ),
    Data[Test] = "B&W No Pics"
)

VAR ColHigh =
CALCULATE(
    SELECTEDVALUE( Data[Significance High] ),
    Data[Test] = "Color & Pics"
)

RETURN
IF(
    BWLow > ColHigh,
    "Yes",
    "No"
) 
  • Brian

Hi Brian

Thanks. I’ll give it a go.

I came up with the following after posting but it seems a bit clumsy.

Is Test Significant? =
IF(
( CALCULATE( [Significance Low], ‘DM20-2 Donors’[Test] = “B&W No Pics” ) >
CALCULATE( [Significance High], ‘DM20-2 Donors’[Test] = “Colour & Pics” ) ) ||
( CALCULATE( [Significance Low], ‘DM20-2 Donors’[Test] = “Colour & Pics” ) >
CALCULATE( [Significance High], ‘DM20-2 Donors’[Test] = “B&W No Pics” ) ),
“Yes”, “No” )

@KieftyKids,

Okay, I didn’t realize from your initial post that this was a two-way test. I’ve revised my measure accordingly:

Sig Test = 

VAR BWLow =
CALCULATE(
    SELECTEDVALUE( Data[Significance Low] ),
    Data[Test] = "B&W No Pics"
)

VAR ColHigh =
CALCULATE(
    SELECTEDVALUE( Data[Significance High] ),
    Data[Test] = "Color & Pics"
)

VAR ColLow =
CALCULATE(
    SELECTEDVALUE( Data[Significance Low] ),
    Data[Test] = "Color & Pics"
)

VAR BWHigh =
CALCULATE(
    SELECTEDVALUE( Data[Significance High] ),
    Data[Test] = "B&W No Pics"
)

RETURN
SWITCH( TRUE(),
    BWLow > ColHigh, "Yes",
    ColLow > BWHigh, "Yes",
    "No"
) 

This is functionally equivalent to yours above. Personally, I like the use of variables and the SWITCH(TRUE() construct to clearly show each condition that will trigger a “Yes”, but that’s just a matter of personal style – both should work.

Note that I just did a screen grab of the data you provided in your initial post, so for me everything is in columns. For you, some will be in measures, so you won’t need the SELECTEDVALUE where you have measures rather than columns.

Hope this is helpful. Full solution file posted below.

Hi Brian

Thank you. Your solution makes a lot more sense.