Need Help with nested IF statements comparing text values between columns.

[shaebert]

Hello! I need help visualizing in a table visual the number of registrations per campaign, but then I need to slice on the account manager AND show campaigns with zero registrations if the campaign is the region is in the reps region. It's just the last part in my previous sentence that I can't figure out. I figure I have to write some IF statements to accomplish this. 

Below is sample data of the tables, what I am currently able to accomplish, and what I am trying to accomplish. 

Tables of data: "<--->" indicates the relationship key between the tables. 

With the data above, I am only able to produce this:

My goal is to produce this, which I need help with:

 

 

 

 

[v-jianboli-msft · Accepted solution]

Hi @shaebert ,

I have modified the measure # Registered, please check:

# Registered = 
CALCULATE(COUNTROWS('Registration Table'),FILTER('AM Region Table',[Rep]=SELECTEDVALUE('REP'[Rep])))+0

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[shaebert]

Thank you for your help @v-jianboli-msft ! When I transferred to the live report, I had to include an additional workaround to make it work with some other variables that weren't in the sample data, but I got it to work. 

[v-jianboli-msft]

Hi @shaebert ,

Please try:

First create a new table for Rep:

Note: This table do not have any relationship with other table

Then use it create a slicer

Apply the measure flag to the table visual's filter:

Flag = 
var _a = SELECTCOLUMNS(FILTER('AM Region Table',[Rep] in ALLSELECTED(REP[Rep])),"Region",[Region])
var _b = CALCULATETABLE(SELECTCOLUMNS('Campaign Location Table',"Region",[Region]),FILTER('AM Region Table',[Rep] in ALLSELECTED(REP[Rep])))
var _c = UNION(_a,_b)
return IF(SELECTEDVALUE('Campaign Location Table'[Region]) in _c,1)

Apply this measure:

# Registered = COUNTROWS('Registration Table')+0

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[shaebert]

Hi @v-jianboli-msft - thanks for your help. I was having issues trying to get your sample file to match mine, but I figured out the issue, I had to adjust the relationship direction between two of the tables. Once I did that, I was able to replicate what you shared. 

When the slicer is filtered on Mike C, it works perfectly. But when it is sliced on other reps, it doesn't show what it is supposed to. Below is what the other data should look like. Any thoughts?

*note that in the Campaign Location Table Campaign4's region in my sample date was "WA," in the sample you provided it said "WI," which is fine, I updated mine to reflect the same. Just want to make sure you're aware.