How can I remove a dimension from a table when it is part of a calculated expression?

[data_RN]

Hello Again, 

I have been trying this all morning, and figured I would put it out there as there has got to be an easy answer that clearly won't just come to me 🙂

Essentially I am trying to build a table that will only show the total number of programs is with a certain assessment that is done .  The end goal is to have a 'heat-map' like display highlighting groups that have a low-level of completion.

I am looking at the current stage of a program and including the program if the assessment for that stage is done.   I can get it to display properly if I display the stage in the table, but once I remove the dimension from my table, the calculation isn't working.

I also tried to just hide the dimension but the list keeps the rows for that dimension and I want it to be a simple (uncollapsed) view similar to this:

Group	List Done Count
	5
a	1
b	3
c	1

As a reference, here is my complete table for the example above:

Group	Program #	Stage	Go-To Seed List Done	Go-To-Alpha List Done	Go-To-Beta List Done	Count (Program)	List Done Count
						9	0
a	2	Alpha	Yes	No	Yes	1	0
a	1	Seed	Yes	Yes	Yes	1	1
a	3	Seed	No	Yes	Yes	1	0
b	4	Beta	Yes	No	Yes	1	1
b	6	Alpha	Yes	Yes	Yes	1	1
b	5	Seed	Yes	Yes	Yes	1	1
c	7	Beta	Yes	No	No	1	0
c	8	Beta	Yes	No	No	1	0
c	9	Alpha	Yes	Yes	No	1	1

Here is the formula I am using for 'List Done Counts':

(If([Stage]='Seed',Count({<[Go-To Seed List Done] = {'Yes'}>}[Program]),
If([Stage]='Alpha',Count({<[Go-To-Alpha List Done] = {'Yes'}>}[Program]),
If([Stage]='Beta',Count({<[Go-To-Beta List Done] = {'Yes'}>}[Program]),0))))

Attached is the dashboard in case that is easier.

Thanks in advance for any guidance!

[sunny_talwar · Accepted solution]

Try using this

RangeSum(
Count({<[Go-To Seed List Done] = {'Yes'}, [Stage] = {'Seed'}>}[Program]),
Count({<[Go-To-Alpha List Done] = {'Yes'}, [Stage] = {'Alpha'}>}[Program]),
Count({<[Go-To-Beta List Done] = {'Yes'}, [Stage] = {'Beta'}>}[Program])
)

[data_RN]

Of course it works just as I was hoping for!

Thank you!

Just curious, is there an easy explanation as to why RangeSum doesn't need for the the dimension to be in the table versus the IF statements needing it to be?

[sunny_talwar]

You don't really need the RangeSum() here... this would work the same way

Count({<[Go-To Seed List Done] = {'Yes'}, [Stage] = {'Seed'}>}[Program])
+
Count({<[Go-To-Alpha List Done] = {'Yes'}, [Stage] = {'Alpha'}>}[Program])
+
Count({<[Go-To-Beta List Done] = {'Yes'}, [Stage] = {'Beta'}>}[Program])

But I like to use RangeSum() because it can handle null better than the plus (+) sign. For example RangeSum(10 + Null()) = 10, but 10 + Null() = Null().

The change I made is to convert If statement to set analysis... if statement didn't work because there were multiple stages for each group. When you said Stage = ...., if worked when stage was a dimension, but when stage is not a dimension.... the chart has multiple stages and nulls out because it doesn't know what exactly to use. To fix this, you can use Aggr() function with Group and Stage as dimension

Sum(Aggr(
(If([Stage]='Seed',Count({<[Go-To Seed List Done] = {'Yes'}>}[Program]),
If([Stage]='Alpha',Count({<[Go-To-Alpha List Done] = {'Yes'}>}[Program]),
If([Stage]='Beta',Count({<[Go-To-Beta List Done] = {'Yes'}>}[Program]),0)))),
Group, Stage))

But why complicate things, when we can simplify them

[data_RN]

Agreed!  and that makes total sense.

Thanks again for your help!

Cheers!