Calculate Visited Frequency with exclude some conditions

[ADSL]

Hi BI Community Team,

I have a data table of visited and applied a measure to caluclate the visited frequency as below.

But we want to be exclude and not calculate if value from customer code are: "1234".

Sample file: https://drive.google.com/drive/folders/1WzBr8h-QQFbZQf1XcdQH3VUBCErlxUAd?usp=sharing 

Here is the measure:

Cnt_Visit =

SUMX(

    VALUES(Visited_Freq[Frequency]),

    Var _cnt = CALCULATE(MAX(Visited_Freq[Index]))

    Return

        SUMX(

            VALUES(Data[SR_CODE]),

            COUNTROWS(

                FILTER(

                    VALUES(Data[CUSTOMER_CODE]),

                Var _dc =

                    CALCULATE(DISTINCTCOUNT(Data[CALL_DATE]))

                Return

                _dc = _cnt || _cnt >8 && _dc >8

                )

            )

        )

)

Any suggestion/advise?

Thanks and Regards,

[AmiraBedh · Accepted solution]

After we discussed your problem, you can apply a filter directly to the visual.

[danextian]

Hi @ADSL 

Try these although the results are the same as your original formula as the values you want to be ecxluded from customer code are not there to begin with.

=
SUMX (
    VALUES ( Visited_Freq[Frequency] ),
    VAR _cnt =
        CALCULATE ( MAX ( Visited_Freq[Index] ) )
    RETURN
        SUMX (
            VALUES ( Data[SR_CODE] ),
            COUNTROWS (
                FILTER (
                    SUMMARIZE (
                        FILTER ( data, NOT Data[CUSTOMER_CODE] IN { "9999", "29100", "1075" } ),
                        Data[CUSTOMER_CODE]
                    ),
                    VAR _dc =
                        CALCULATE ( DISTINCTCOUNT ( Data[CALL_DATE] ) )
                    RETURN
                        _dc = _cnt
                            || _cnt > 8
                            && _dc > 8
                )
            )
        )
)

=
SUMX (
    VALUES ( Visited_Freq[Frequency] ),
    VAR _cnt =
        CALCULATE ( MAX ( Visited_Freq[Index] ) )
    RETURN
        SUMX (
            VALUES ( Data[SR_CODE] ),
            COUNTROWS (
                FILTER (
                    VALUES ( Data[CUSTOMER_CODE] ),
                    VAR _dc =
                        CALCULATE ( DISTINCTCOUNT ( Data[CALL_DATE] ) )
                    RETURN
                        ( _dc = _cnt
                            || _cnt > 8
                            && _dc > 8 )
                            && NOT Data[CUSTOMER_CODE] IN { "9999", "29100", "1075" }
                )
            )
        )
)

[ADSL]

Hi @danextian,

It's still be included after trying your measure feedback, and as the screenshot below.

Any suggestion?

Thanks and Regards,

[AmiraBedh]

I checked the pbix file and I see that the Data table and the visited_freq are linked ?

[ADSL]

Hi @AmiraBedh,

Yes, it's not linked because I don't know how to link.

Any suggestion how to do it?

Thank and Regards,

[AmiraBedh]

How did you generate your visual ? You need to have a column in DATA table (I am assuming it is your fact table) to link it with the visited_freq.

[ADSL]

Hi @AmiraBedh,

In Data table, it doesn't have the column of visited_freq. we just created the measure to calculate it.

For visualization, we just drag and drop as you see the sample file sharing. But I remember that it can be working and showing even it have no relationship when tried a few time.

Could you please help to make it for this case? Thank you in advance.

Thanks and Regards,

[Anonymous]

Cnt_Visit2 =

SUMX(

    VALUES(Visited_Freq[Frequency]),

    VAR _cnt = CALCULATE(MAX(Visited_Freq[Index]))

    RETURN

        SUMX(

            VALUES(Data[SR_CODE]),

            COUNTROWS(

                FILTER(

                    VALUES(Data[CUSTOMER_CODE]),

                    VAR _dc = CALCULATE(DISTINCTCOUNT(Data[CALL_DATE]))

                    RETURN

                        NOT (

                            Data[CUSTOMER_CODE] IN {"9999", "29100", "1075"}

                            || (_dc = _cnt || (_cnt > 8 && _dc > 8))

                        )

                )

            )

        )

)