Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Solved! Go to 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.
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.
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.
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |