Multiple alternate states expression/comparison question

[phcaptjim]

I have a view where I have 7 different alternate states.  The user can find a population of patients in each 'state' then I have a chart that compares the 7 states and shows how many states each patient falls into.

Here are the column headers:

Each column (C1, C2, etc) has an expression like this:

=Sum({C1}[Patient Counter])

There will be a 1 if the patient is in that state or a zero if not.

Then I have a 'Sum' column that reports how many states each patient falls into:

=C1+C2+C3+C4+C5+C6+C7

My final column is a link to the chart in our electronic health record.  Right now I am getting some blank values at random points and I can't figure out why.  Here is the expression I am using for that column:

=If(C7=1,[Patient MRN]&'<url>'&[EPIC Patient Hyperlink],

If(C6=1,[Patient MRN]&'<url>'&[EPIC Patient Hyperlink],

If(C5=1,[Patient MRN]&'<url>'&[EPIC Patient Hyperlink],

If(C4=1,[Patient MRN]&'<url>'&[EPIC Patient Hyperlink],

If(C3=1,[Patient MRN]&'<url>'&[EPIC Patient Hyperlink],

If(C2=1,[Patient MRN]&'<url>'&[EPIC Patient Hyperlink],

If(C1=1,[Patient MRN]&'<url>'&[EPIC Patient Hyperlink]

)))))))

Since I have to put this hyperlink as an expression (I wish we could do dimensions as expressions) I have to use an expression so I don't get every patient.  This chart is NOT in an alternate state since I need it to ready all the states.  Everything works well except that last column.  Does anyone have an neat ideas on this?

Thanks!

[phcaptjim]

Yes.  The reason is the 1's and 0's are from the alternate states where the other field is not.

[sunny_talwar]

Why don't you make MRN from alternate state also?

[phcaptjim]

I'm not sure how that would work.  Each state (C1, C2, etc) will have different lists of MRN's but the chart is a master list of all MRN's from the states.  For example....

MRN

111     --comes from C1

222     --comes from C2

333     --comes from C3

444     --comes from C3 and C4

Also, I just realized the original solution doesn't always work.

Notice some hyperlinks are underlined and some are not.  I didn't realize this would be so challenging.

NOTE: These values have been scrambled and do not contain real data.

[sunny_talwar]

May be this

Only({C1+C2+C3+C4+C5+C6+C7}[Patient MRN])

[bvssudhakar]

Hi Sunny,

Here, I am having also one Query like this

I have 6 alternate states named as set1, set2, set3, set4, set5, set6 (These 6 are Current Selection boxes i.e i used state name function) and i have 4 list boxes named as year, Category, Product name, Customer (Now these 4 are with another alternate state named as 'proposed').

Now i have created one straight table with these columns year, Category, Product name, Customer, Sum(Quantity).

But, i want to show the values when we pass our selections to that sets those values only i want to see.

for that i used sum({Set1 + Set2 + Set3 + Set4 + Set5 + Set6} Quantity). Here, also i am getting problem.. when i pass the selections to all sets that time only it is giving correct result.

My requirement is if i pass my selections to only set1 then also i wnat correct result / if pass the selections to 2 or 3 sets then that time also i want correct result

For that How can i write my expression can you give me the suggestion apart from i used these below expressions also:

1.

=sum({<[Sales Person]=Set1 ::[Sales Person],Customer=Set1 ::Customer>} Quantity)

+sum({<[Sales Person]=Set2 ::[Sales Person],Customer=Set2 ::Customer>} Quantity)

+sum({<[Sales Person]=Set3 ::[Sales Person],Customer=Set3 ::Customer>} Quantity)

+sum({<[Sales Person]=Set4 ::[Sales Person],Customer=Set4 ::Customer>} Quantity)

+sum({<[Sales Person]=Set5 ::[Sales Person],Customer=Set5 ::Customer>} Quantity)

2.

=sum({<[Sales Person]=Set1 ::[Sales Person],Customer=Set1 ::Customer>} Quantity)

&sum({<[Sales Person]=Set2 ::[Sales Person],Customer=Set2 ::Customer>} Quantity)

&sum({<[Sales Person]=Set3 ::[Sales Person],Customer=Set3 ::Customer>} Quantity)

&sum({<[Sales Person]=Set4 ::[Sales Person],Customer=Set4 ::Customer>} Quantity)

&sum({<[Sales Person]=Set5 ::[Sales Person],Customer=Set5 ::Customer>} Quantity)

3.

=num(sum({(vFilterStates)} Quantity))  ---------------(vFilterStates is the variable which i created)

(This is not giving any result)

4.

=Sum({(vFilterStates)} Quantity)

(This is also not giving any result)

For "vFilterStates" Variable i used this expression

= If( len(GetCurrentSelections('','',1,'Set1'))

+ len(GetCurrentSelections('','',1,'Set2'))

+ len(GetCurrentSelections('','',1,'Set3'))

+ len(GetCurrentSelections('','',1,'Set4'))

+ len(GetCurrentSelections('','',1,'Set5'))

+ len(GetCurrentSelections('','',1,'Set6'))

     =0, '$',

  if(len(GetCurrentSelections('','',1,'Set1'))>0, 'Set1+','')

& if(len(GetCurrentSelections('','',1,'Set2'))>0, 'Set2+','')

& if(len(GetCurrentSelections('','',1,'Set3'))>0, 'Set3+','')

& if(len(GetCurrentSelections('','',1,'Set4'))>0, 'Set4+','')

& if(len(GetCurrentSelections('','',1,'Set5'))>0, 'Set5+','')

& if(len(GetCurrentSelections('','',1,'Set6'))>0, 'Set6+',''))

Can you please help me out from this

[sunny_talwar]

Would you be able to share a qvw sample to show your issue?