Calculated Column from Measure

[saud968]

I am using this Dax as a measure - 

S =

CALCULATE(

    COUNT('Colleague Events'[SEARCH_ID]),

    FILTER(

        'Colleague Events',

        'Colleague Events'[CAUSE2] IN {"didyoumeanAutomatic", "didyoumeanClick", "searchboxSubmit", "searchboxAsYouType", "omniboxSearch", "omniboxAnalytics", "omniboxFromLink", "omniboxField", "searchFromLink"}

    )

)

But when I use the same dax for the calculated column it is not giving the correct output. Please download the Excel files 

https://drive.google.com/file/d/1dGREcWSPPDGj8MBnQNAcqeKV_s1swcG5/view?usp=drivesdk - This one shows incorrect count when using the dax in calculated Column

https://docs.google.com/spreadsheets/d/14f-X4TZkshu0rgOi_Cn22y8C8l8chgXz/edit?usp=drivesdk&ouid=1093...

@Ahmedx @Fowmy @Ritaf1983 @amitchandak @Idrissshatila 

[DataInsights]

@saud968,

Try this calculated column. The ALL calculate modifier is necessary to clear the filter context, and then apply the desired filter.

S =
CALCULATE (
    COUNT ( 'Colleague Events'[SEARCH_ID] ),
    ALL ( 'Colleague Events' ),
    'Colleague Events'[CAUSE2]
        IN {
            "didyoumeanAutomatic",
            "didyoumeanClick",
            "searchboxSubmit",
            "searchboxAsYouType",
            "omniboxSearch",
            "omniboxAnalytics",
            "omniboxFromLink",
            "omniboxField",
            "searchFromLink"
        }
)

[saud968]

I tried unfortunately still getting the incorrect output

[v-xinruzhu-msft]

S =
CALCULATE (
    COUNT ( 'Colleague Events'[SEARCH_ID] ),
    FILTER (
        'Colleague Events',
        'Colleague Events'[Colleague Name]
            = EARLIER ( 'Colleague Events'[Colleague Name] )
            && 'Colleague Events'[CAUSE2]
            IN {
                "didyoumeanAutomatic",
                "didyoumeanClick",
                "searchboxSubmit",
                "searchboxAsYouType",
                "omniboxSearch",
                "omniboxAnalytics",
                "omniboxFromLink",
                "omniboxField",
                "searchFromLink"
            }
    )
)

If the following information cannot meet your requirement, can you provide some sample output you want?

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[saud968]

Also, please accept it as a solution for me. 

[saud968 · Accepted solution]

The "Colleague Name" is from another column. However, I was able to resolve the issue by separating the filter. 

IsDesiredCause =
    'Colleague Events'[CAUSE2] IN {"didyoumeanAutomatic", "didyoumeanClick", "searchboxSubmit",
                                   "searchboxAsYouType", "omniboxSearch", "omniboxAnalytics",
                                   "omniboxFromLink", "omniboxField", "searchFromLink"}
Colleague Searches =
CALCULATE (
        COUNT('Colleague Events'[SEARCH_ID]), ALLEXCEPT('Colleague Events', 'Colleague Events'[CAUSE2]), 'Colleague Events'[IsDesiredCause] = TRUE()
)
This gave the correct output

Best Regards

Saud Ansari

If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!