Forum Discussion

JosephS's avatar
JosephS
Regular Visitor
6 years ago
Solved

Join Against Unfiltered Column

I've got two tables, Sales and Targets, whose data I want to present in a report.  On the report I have a Line Chart presenting Sales by Territory.  I also have a Card with the Quarter Target.  Initially, this Card should show the overall Target (the one with the NULL Territory).  Once the user selects (via legend or line) a highlight on Territory, the Card should change to show the Territory Target.

 

I'm relatively new to Power BI.  I'm familiar with the use of ALL and ALLEXCEPT.  I'm not clear on how I would achieve what I'm looking for above.  Any thoughts?

 

Thanks.

Joseph

 

Sales  
TerritorySalesYoY%
MidWest150012.5
Eastern200010.4
Pacific30008.8

 

Targets  
QuarterTerritoryTarget (YoY%)
Q1FY20MidWest15
Q1FY20Eastern10
Q1FY20Pacific14
Q1FY20 13.5
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    Perhaps:

     

     

    Measure =
  VAR __Territory = MAX('Sales'[Territory])
RETURN
  IF(HASONEVALUE('Sales'[Territory]),
    LOOKUPVALUE('Targets'[Target YoY%],'Targets'[Territory],__Territory]),
    MAXX(FILTER('Targets',ISBLANK('Targets'[Territory])),'Targets'[Target YoY%])
  )

     

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    Perhaps:

     

     

    Measure =
  VAR __Territory = MAX('Sales'[Territory])
RETURN
  IF(HASONEVALUE('Sales'[Territory]),
    LOOKUPVALUE('Targets'[Target YoY%],'Targets'[Territory],__Territory]),
    MAXX(FILTER('Targets',ISBLANK('Targets'[Territory])),'Targets'[Target YoY%])
  )

     

    • JosephS's avatar
      JosephS
      Regular Visitor
      6 years ago

      Thanks for responding.  I'm working on understanding and implementing your solution.  I'll let you know how it goes.