Forum Discussion
Using measure to compare two columns in a matrix
As the title suggests, I have two columns in a matrix that I need to compare. Both are date columns and I am trying to display the difference between the two. The issue I'm having, is when I try to directly compare the two columns, the matrix is populated with every single row in my table. For example, this is the result I am looking for, where "Start" and "Baseline Start" are the columns I am pulling in from my data table, and "Start Variance" is a measure:
| WBS | Start | Baseline Start | Start Variance |
| WBS1 | 3/1/2023 | 1/16/2023 | -31 |
| WBS2 | 3/1/2023 | 1/16/2023 | -31 |
Work Package 2 | 3/1/2023 | 1/16/2023 | -31 |
Work Package 5 | 6/3/2024 | 2/8/2024 | -80 |
Work Package 6 | 2/1/2024 | 5/3/2024 | 65 |
But when I try and create a start variance measure like this:
Start Variance =
IF([Start] = [BL Start], 0, NETWORKDAYS([Start], [BL Start]))
Then I end up with a matrix that lists every single row in my data table, which is what I do not want:
| WBS | Start | Baseline Start | Start Variance |
| WBS1 | 3/1/2023 | 1/16/2023 | -31 |
| WBS2 | 3/1/2023 | 1/16/2023 | -31 |
Work Package 1 | 0 | ||
Work Package 2 | 3/1/2023 | 1/16/2023 | -31 |
Work Package 3 | 0 | ||
Work Package 4 | 0 | ||
Work Package 5 | 6/3/2024 | 2/8/2024 | -80 |
Work Package 6 | 2/1/2024 | 5/3/2024 | 65 |
So how do I create a measure that compares these two columns?
vsorensen Try:
Start Variance = SWITCH( TRUE(), [Start] = BLANK(), BLANK(), [Start] = [BL Start], 0, NETWORKDAYS([Start], [BL Start]) )
4 Replies
- Greg_Deckler
Community Champion
vsorensen Try:
Start Variance = SWITCH( TRUE(), [Start] = BLANK(), BLANK(), [Start] = [BL Start], 0, NETWORKDAYS([Start], [BL Start]) )- Greg_Deckler I had to put MIN() around my column references but other than that it worked, thank you! Can you explain why this works? I had tried the code below before in one of my many attempts to figure it out but with no luck.
SWITCH(
TRUE(),
MIN([Start]) = MIN([BL Start]), 0,
ISBLANK(MIN(p6_data[BL Start])), BLANK(),
NETWORKDAYS([Start], [BL Start])
)- Greg_Deckler
vsorensen Oh, fair, I guess it is a measure and all. Basically, SWITCH TRUE is a nifty way of writing nested IF statements more cleanly. So, with SWITCH TRUE, you list out logical tests which are evaluated in order. If a logical test is met, then the corresponding value is returned so think of it like this:
SWITCH( TRUE(),
<Logical Test 1>, <Result if Logical Test 1 is true>,
<Logical Test 2>, <Result if Logical Test 2 is true>,
<Result if all other tests evaluate to false>
