Logic problem

[woshua5550]

Hi there

This is a simple table what my souce table looks like

Below is what i would like to achieve:

If i choose AF1 in the filter, then we hope it can return the number:2

Because once we chosed AF1, then all the values 1 can been seen as 0. In this situation, ID 4 and 7 will have 0 for all hard checks, so 2 cases is the number we need

If we choose UW1 and UW2 in the filter part, then it should return the number:4

Because UW1 and UW2 are chosed in the filter,  all the values in thest two column will change to 0. In this situation: ID 1, ID 2, ID 8 and ID 9 will have all the column values with 0

plz help me , thx in advance.

[lironbaram · Accepted solution]

hi

there is quite an easy solution

in your load script cross table your source table for example

[Sheet1$]:

CrossTable (Status,Data,1)

LOAD [ID],

  [UW1],

  [UW2],

  [UW3],

  [UW4],

  [AF1],

  [AF2],

  [AF3],

  [AF4]

FROM [lib://AttachedFiles/Book1.xls]

(biff, embedded labels, table is Sheet1$);

then in your model

crate a calculated dimension with this expression

aggr(if(sum({<Status=e(Status)>}Data)=0,ID),ID)

this will get you your answer

[woshua5550]

Hi Liron

thanks for your reply , I followed your step but i still can't get the right answer

counld you plz attach your test file ?

thx

[rido1421]

Hi Dave

Could you perhaps post the sample data?

What is the logic behind your "Hard Checks" , how do you get the result 2 when selecting AF1?

The way I understand it , you want to count all "1" in the selected field when all the other fields =0?

[woshua5550]

Hi , problem solved

"count(aggr(if(sum({<Status=e(Status)>}Data)=0,ID),ID))" is what i want

thank you for your advice , it's quite helpful

[woshua5550]

Hi

when i selected AF1 , all the values in AF1 can be seen as 0 ,  i want to count rows that all columns is 0 , so the answer is 2.