Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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?
Solved! Go to Solution.
@vsorensen Try:
Start Variance =
SWITCH( TRUE(),
[Start] = BLANK(), BLANK(),
[Start] = [BL Start], 0,
NETWORKDAYS([Start], [BL Start])
)
@vsorensen Try:
Start Variance =
SWITCH( TRUE(),
[Start] = BLANK(), BLANK(),
[Start] = [BL Start], 0,
NETWORKDAYS([Start], [BL Start])
)
SWITCH(
TRUE(),
MIN([Start]) = MIN([BL Start]), 0,
ISBLANK(MIN(p6_data[BL Start])), BLANK(),
NETWORKDAYS([Start], [BL Start])
)
@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>
@Greg_Deckler right, I am familiar with the SWITCH TRUE formula, my question is if you had any insight as to why the SWITCH formula in my last message did not work while the one you provided did. From what I can tell, the two are effectively the same.
User | Count |
---|---|
123 | |
69 | |
67 | |
58 | |
52 |
User | Count |
---|---|
183 | |
92 | |
67 | |
62 | |
53 |