set analysis

[Tool_Tip]

Dear experts

How to rewrite below measure expression using set analysis only without using IF

Count(
DISTINCT
If(
[START_DATE] <= '$(vMonthEndTest)'
and [status] = 0
and (
[IsActive] =1
or [END_DATE] >= '$(vMonthStart)'
),
ID
)
)

[marcus_sommer]

You may try something like this:

Count({< [START_DATE] = {"<= '$(vMonthEndTest)'"}, [status] = {0} > * 
           (< [IsActive] = {1} > + < [END_DATE] = {">= '$(vMonthStart)'"}>) } DISTINCT ID)

[Nagaraju_KCS]

Try this

Count(

DISTINCT{<START_DATE = {"<= $(vMonthEndTest)"},status = {0},ID = {<IsActive = {1}>

+

<END_DATE = {">= $(vMonthStart)"}>}>}ID)

[Chanty4u]

Try this 

Count(

    DISTINCT

    {

        <

            START_DATE = {"<= $(vMonthEndTest)"},

            status = {0},

            IsActive = {1} + END_DATE = {">= $(vMonthStart)"}

        >

    }

    ID

)

[Tool_Tip]

Dear all

Thank u for your response but none of the above solution works for me

[Lech_Miszkiewicz]

Hi @Tool_Tip 

Looking at the logic of the expression I think @marcus_sommer gave you correct answer as his set analysis replicates logical conditions. Make sure you have those parenthesis in right spot to give proper precedence for * and + on sets as Marcus did.

Also very helpful would be outcome with those fields in straight table and your IF() statement measure next to set analysis equivalent. That is usual way of spotting a difference and seeing where things go wrong. Obviously we dont have that visibility so at least you may have a hint regarding scenarios where this is not working.

We also dont know your data model. So depending on relationships on data you may get different results. It would be helpful to understand where all the fields you use in your expression are in your data model and how they are associated as that may play role too.

My suspicion is that your IF statement does the row level condition and when using set analysis we are working wit set operations on datasets.

cheers