I'm not 100% sure of your requirements, but I have reconfigured your parameters to work off calculated fields, where you had it the other way around. I only did the first five, but this should give you the gist.
Step 1 - Create demos or labs parameter
Step 2 - Create demos or labs calculated field to display desired measure
Step 3 - create calculated fields for your next parameter - note the use of the [demos or labs] calculated field within this calculated field
These will likely end up in a parameter control too, but I wasn't sure where you were going with this...
HTH 10.4 attached
Joshua.twbx 104.5 KB
That's a more complicated way to compute that value than needed, but it works. You could do the same thing for [CASE Labs] and then use that.
But I used LOD to calc the value. See Calculation1 (for Demos) and Calculation2 (for Labs) in the attached. I plunked it into tooltips, but you can use it in the title as well. One calc, no matter what param value the user selects. And you can throw away the # of Demos param.
lab count_v10.4.twbx 101.0 KB
Thanks Joe for sending along this calculation. It's super efficient and it worked!
Followup: I tried creating a CASE Labs calc field but ran into issues. Did you mean that it's possible to create this similarly to the CASE Demos calc field? I'm missing one or two pieces to the puzzle. Any chance you can replicate a CASE Labs field that works using that method? (I'd like to develop a better mastery of the method I started with for practice) much appreciated. Josh
I had to dig into what was really happening in that CASE statement for demos.
That calc works because you have a 1:1 correspondence between rows and demo titles.
For LABS, you have multiple copies of a lab name under each research name. (Thus, you have multiple demos per lab in many cases.)
So the CASE logic actually won't work for Labs.
At first I tried making the CASE by excluding null values of lab name. And then by excluding lab name = " ". I had to make sheet 2 to really see what the data looks like. And that's when I realized that a lab applies to multiple demos.
So I abandoned the whole CASE logic, and did another LOD calc to count up how many dups of a given lab there are under any study. (See calculation3. Notice that I subtracted 1 from the sum. Every occurrence of a lab will have at least one occurrence. Since I'm counting dups, I subtract the first occurrence.)
Now in [CASE labs] I take the sum of demos, and subtract out the sum of duplicates, and that gives me the number of labs.
Note: Your CASE approach will fail if ever you have duplicate demo names under a given research area.
Thanks for digging into this Joe. You answered my main question about why a CASE Labs statement couldn't simply replicate the CASE Demos statement. (multiple copies of a lab name under each research name.) Thanks for explaining the alt approach and how it works. This will help me tackle future projects similar in nature. The workbook wasn't attached so I couldn't see calc 3, but I'd love to see the approach. thx again.