Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The 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.

Reply
vsorensen
Frequent Visitor

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:

WBSStartBaseline Start   Start Variance
WBS13/1/2023   1/16/2023-31
     WBS23/1/20231/16/2023-31

          Work Package 2   

3/1/20231/16/2023-31

          Work Package 5   

6/3/20242/8/2024-80

          Work Package 6   

2/1/20245/3/202465

 

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:

WBSStartBaseline Start   Start Variance
WBS13/1/2023   1/16/2023-31
     WBS23/1/20231/16/2023-31

          Work Package 1   

  0

          Work Package 2   

3/1/20231/16/2023-31

          Work Package 3   

  0

          Work Package 4   

  0

          Work Package 5   

6/3/20242/8/2024-80

          Work Package 6   

2/1/20245/3/202465

 

So how do I create a measure that compares these two columns?

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

@vsorensen Try:

Start Variance = 
  SWITCH( TRUE(),
    [Start] = BLANK(), BLANK(),
    [Start] = [BL Start], 0, 
    NETWORKDAYS([Start], [BL Start])
  )


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

4 REPLIES 4
Greg_Deckler
Super User
Super User

@vsorensen Try:

Start Variance = 
  SWITCH( TRUE(),
    [Start] = BLANK(), BLANK(),
    [Start] = [BL Start], 0, 
    NETWORKDAYS([Start], [BL Start])
  )


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

@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])
)
 

@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>

 

 



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

@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.

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.