Multiple Keepfilters

[charleshale]

Hi.  I have an interested measure that works well enough with KEEPFILTERS() but it would be even more versatile if I could apply multiple KEEPFILTERS to it.   Does anyone have any advice about how to apply multiple KEEPFILTERS() to a SUMX () measure?

Thank you.   

Charlie

[JarroVGIT]

Hi @charleshale ,

You might want to check out this article. It goes into the workings of KEEPFILTERS in combination with SUMX and I have found it extremely helpful in the past 🙂

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

[mwegener]

Hi @charleshale ,

could you post a example?

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.

[charleshale]

Thanks, @mwegener.  This one is hard to sample.  It's extremely involved.  It involves the following measures (see code at bottom).

Essentially, the ALL() filters when measures get embedded in other measures sometimes dont tack all the way through because I've got a lot of differently-sized tables.  Perfect for KEEPFILTERS(), right?

Ideally, though, instead of   SUMX ( KEEPFILTERS ( VALUES ( //TABLE[COLUMN] to keep filters on// ) ), //MEASURE// )
I could engineer SUMX ( KEEPFILTERS ( // multiple tables that could touch the measure where I need the filters kept // ) ), //MEASURE ).

This is not a critical one at all.     Just trying to figure out advanced KEEPFILTERS ().  Thank you.

Best,

Charlie

\v2_FTE\1 = 
VAR Step1 =
    CALCULATETABLE (
        ADDCOLUMNS (
            SUMMARIZE ( all_articles_ever, DimDate[Year], all_articles_ever[user_id] ),
            "FirstDate", FIRSTDATE ( all_articles_ever[date] ),
            "LastDate", LASTDATE ( all_articles_ever[date] ),
            "^Days_Granular", [^Days_Granular],
            "^Days_alt", [^Days_alt],
            // This the same measure as ^Days_Granual but uses the date table dimdate to determine the available days in a period ---- COUNTROWS(DATESBETWEEN(DimDate[date],FIRSTDATE(DimDate[date]),LASTDATE(DimDate[date])))
            "DailyLocAvg-Articles", [__DailyLocAvg_Art],
            "#Art", [**#Posts_Articles_Patch_AllStaff]
        ),
        all_articles_ever[Lkup_RoleID] IN {4, 5},
        all_articles_ever[type] = "article",
        // all_articles_ever[user_id] <> "23215973" || all_articles_ever[user_id] <> "23083459" || all_articles_ever[user_id] <> "23586758", //This doesnt work. 
         NOT (all_articles_ever[user_id] IN {"23215973", "23083459",  "23586758"}),  
        // could also use && but this seems faster
        ALL ( 'JSON 20181207' )
    ) -- Step 2 - Add Column that qualifies FTEs as needing to have produced >2.5 articles and worked for 32+ days ....and then gives this a haircut on FTEs to the extent their days per the dates on the article are less the dates in the period
VAR Step2 =
    ADDCOLUMNS (
        Step1,
        "@FTE Alt",
        VAR _FTELocalEditManual =
            AND ( [DailyLocAvg-Articles] >= 2.5, [^Days_Granular] > 32 )
        VAR _PctYr360 =
            ROUND ( MIN ( 1, [^Days_Granular] / MIN ( 360, [^Days_alt] ) ), 1 )
        RETURN
            _FTELocalEditManual * _PctYr360
    )
VAR Step3 =
    SUMX ( Step2, [@FTE Alt] )
RETURN
    Step3

\v2_FTE\3 "ALL"_#ArtDstnct_Ed = CALCULATE(
    [^#Articles],
    ALL('JSON 20181207'[wbs_code] ) 
    ) //keep as all arts since table restricts to patch at source)   Dont use deduped since sumn of WBS level (deduped) will be more than the deduped author level (same article can be unique to multiple patches but will be only 1 to author)

\V2_FTE\4 (Req Ed_WBS Filter) = ([^#Articles])/[\v2_FTE\3 "ALL"_#ArtDstnct_Ed]*[\v2_FTE\1]   //keep as articles distinct since that's the pro rata that matters and table restricts to eds earlier
//WBS level is deduped at that level....but author level dedupe means few stories at that level

\v2_FTE\ FINAL = //seems to work.   needs roles 4, 5 selected
SUMX(
    KEEPFILTERS(VALUES(p_user_dump[first_last_name])),
    [\V2_FTE\4 (Req Ed_WBS Filter)]
)

[mwegener]

Hi @charleshale,

 I'm not deep enough in this topic to give you good advice here.