Most Efficient Query

[Anonymous]

Hello All, 

Im trying to get an output of 0 or 1 for flag using a calculated column, code: 

CI_c =

if(SUMX(

SUMMARIZE(

CI,

CI[PPLCODE],

"sum of data", CALCULATE(COUNT(CI[PPLCODE]), FILTER(CI, CI[PPLCODE]='IRR Retina'[PPL CD]

&& CI[UPDATED_ON_DT].[Date]>'IRR Retina'[Contract Start Date_c]&& CI[UPDATED_ON_DT].[Date]<='IRR Retina'[Transaction date_c]

))),

[sum of data]

)>0,1,0)

Can someone help optimize this for me, im not sure where to begin. 

Thanks,

Matt 

[v-cazheng-msft · Accepted solution]

Hi @Anonymous 

Please try this Calculated column.

CI_c =

IF (

    CALCULATE (

        COUNT ( CI[PPLCODE] ),

        FILTER (

            CI,

            CI[PPLCODE] = 'IRR Retina'[PPL CD]

                && CI[UPDATED_ON_DT].[Date] > 'IRR Retina'[Contract Start Date_c]

                && CI[UPDATED_ON_DT].[Date] <= 'IRR Retina'[Transaction date_c]

        )

    ) > 0,

    1,

    0

)

Then, the result should look like this.

Attached the pbix file as reference. If it doesn’t work, could you please provide some sample data used by this formula and let me know the relationship between your tables? Thanks in advance!

In addition, you can use Performance Analyzer to help you optimize your calculation. For more details about Performance Analyzer, please refer to Use Performance Analyzer to examine report element performance.

Best Regards,

Community Support Team _ Caiyun

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[VahidDM]

Hi @Anonymous 

Can you post sample data as text and expected output?

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
4. Relation between your tables

Appreciate your Kudos!!
LinkedIn:www.linkedin.com/in/vahid-dm/

New Year Power BI eCard:

https://community.powerbi.com/t5/Data-Stories-Gallery/Happy-New-Year/td-p/2266398