Compare multiple fields using Set Analysis

[PPAT1981]

Hope somebody here can help me. I try to create a calculated mesaure based on the fields from few separate tables.

In the pivot table I need to count distinct  IDs if the below four conditions are TRUE.

i tried something like this but this part ([colD]=[ColF]) of set analysis formula doesn't work. 

count({<ColA={"Category1"}, colB -= {""},colC -= {""},[colD]=[ColF]>} distinct IDs)

Requirements:

1) colA = ("Catgory1")
2) colB is not NULL
3) colC is not NULL
4) colD.value = ColF.value

if above four conditions are true, count distinct IDs..

Let me know if this doesn't make sense. thanks

[Vegar · Accepted solution]

You can't explicit select NULL using set analysis,  but in your case you could do a work around by selecting union everything.

Please explain what you mean by colD=ColF. Is it a row by row comparison or do you want all colD rows that contains any of the ColF values in your selection.

Try one of these:

count({<ColA={"Category1"}, colB *= {"*"},colC *= {"*"},[colD]=p([ColF])>} distinct IDs)

count({<ColA={"Category1"}, colB *= {"*"},colC *= {"*"},RowIdentifierField={"=[colD]=[ColF]"}>} distinct IDs)

[PPAT1981]

Thanks for your answer, its row by row comparison with  colD=ColF.

[abhijitnalekar]

Agree with Vegar.

Just want to tweak the formula to handle Null.

use coalesce(colB,1) as ColB,coalesce(colC,1) as ColC.

and try set analalysis as below

count({<ColA={"Category1"}, colB -= {"1"},colC -= {"1"},[colD]=p([ColF])>} distinct IDs)

count({<ColA={"Category1"}, colB -= {"1"},colC -= {"1"},RowIdentifierField={"=[colD]=[ColF]"}>} distinct IDs)