CODEWITHSUNDEEP
powerbidax
Stringfellow
Stringfellow

Reputation: 2908

How to Perform a Full-Outer Join on Two Separate, Filtered Tables using DAX?

I have an original table named Error, and two additional tables (ErrorBefore and ErrorAfter) derived from the original (e.g. ErrorAfter = ALLSELECTED('Error')). I want to compare values from a 'before' version with an 'after' version, with the different version picked by slicer with 'Single select' on. That's working okay. Now I want to perform a full-outer join on the two results, joining on the Message column. The image below shows the result I have so far, with a fabricated table at the bottom of what I'm trying to achieve. I've tried using NATURALLEFTOUTERJOIN and GENERATE but they either don't give the result that I seek. Does anyone know how to perform the join?

PBIX share here.

enter image description here

Upvotes: 1

Views: 1136

Answers (1)

RADO
RADO

Reputation: 8148

First, change your data model to this:

enter image description here

I removed all your derived tables and relations, and instead created 2 tables like this:

Version Before = DISTINCT('Error'[Version])

Version After = DISTINCT('Error'[Version])

Both tables should have no relations with the Error table.

Then, create a measure:

Message Count = COUNT('Error'[Message])

You should always create measures yourself, never use Power BI auto-aggregations.

Next, create a measure for "Before" count"

Message Count Before = 
  VAR Version_Before = SELECTEDVALUE('Version Before'[Version])
RETURN
  CALCULATE([Message Count], 'Error'[Version] = Version_Before)

and, similarly:

Message Count After = 
  VAR Version_After = SELECTEDVALUE('Version After'[Version])
RETURN
  CALCULATE([Message Count], 'Error'[Version] = Version_After)

Finally, adjust your visuals:

  • Slicer "Before" should be based on table "Version Before"
  • Slicer "After" should be based on table "Version After"
  • Charts and tables should use "Message Count Before" and "Message Count After" measures in values
  • Add another table with messages and both measures

Result:

enter image description here

Upvotes: 2

Related Questions