Filter items which are not max id

[lokosrio]

hi,

to get sum of distribution divided by countrows groupped by region_id of latest visit I have a calculation like this:

var _sum =
SUMX(VALUES( fact_table[region_id] ), LASTNONBLANKVALUE(fact_table[visit_id],
        SUM(fact_table[distribution_flag])))
var _count =
SUMX(VALUES( fact_table[region_id] ), LASTNONBLANKVALUE(fact_table[visit_id],
        COUNTROWS(fact_table)))
return
_sum / _count

right now on the table visualization the result looks like this:

product_id	last_visit_id	period	distribution
prod_1	lv_1	2021P5	80%
prod_2	lv_1	2021P5	60%
prod_3	lv_3	2021P11	40%
prod_4	lv_3	2021P11	20%
prod_5	lv_3	2021P11	90%

What I want to achieve is to filter those products, which are not max of last_visit_id, for example:

1. If I choose period 2021P1 to period 2021P5 then I will see:

product_id	last_visit_id	period	distribution
prod_1	lv_1	2021P5	80%
prod_2	lv_1	2021P5	60%

2. If I choose period 2021P1 to period 2021P11 then I will see:

prod_3	lv_3	2021P11	40%
prod_4	lv_3	2021P11	20%
prod_5	lv_3	2021P11	90%

Could you please help me with this?

[v-xiaotang]

Hi @lokosrio 

Not sure how the results come, could you post some sample data & the expected result? Thanks 🙂

Best Regards,

Community Support Team _Tang

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

[lokosrio]

@Greg_Deckler thank you for the answer, I get the point and modified a little the formula to get what I want:

Distribution Max Measure= 
var _sum =
SUMX(VALUES( fact_table[region_id] ), LASTNONBLANKVALUE(fact_table[visit_id],
        SUM(fact_table[distribution_flag])))
var _count =
SUMX(VALUES( fact_table[region_id] ), LASTNONBLANKVALUE(fact_table[visit_id],
        COUNTROWS(fact_table)))
VAR __Table = ALLSELECTED('fact_table')
VAR __Max = CALCULATE (
        MAX ( 'fact_table'[visit_id] ),
        FILTER (
            ALLSELECTED ( 'fact_table' ),
            'fact_table'[region_id] = SELECTEDVALUE ( 'fact_table'[region_id] )
        )
    )
VAR __Current = MAX('fact_table'[visit_id])

RETURN
   IF(__Current = __Max, var _sum / _count, BLANK())

 but for some reason this measure is not aggregating properly:

It works fine when visit_id field is added to the table, but the point is not to show visit_id and period.

Now when I choose for example periods 2021P1 to 2021P11:

product_id	last_visit_id	period	distribution
prod_1	lv_1	2021P5
prod_2	lv_1	2021P5
prod_3	lv_3	2021P11	100%
prod_3	lv_4	2021P11	0%

Right now for prod_3 it shows 0%, but the visit_id and period will not be shown in the table, so the result should be:

product_id	distribution
prod_1
prod_2
prod_3	50%

Could you please help me with this?

[Greg_Deckler]

@lokosrio Could you post some sample raw data as text? Where does the 50% come from in you last example?

[Greg_Deckler]

@lokosrio This is sort of like a complex selector based on a lookup min/max measure like:

Distribution Max Period Measure = 
    VAR __Table = ALLSELECTED('Table')
    VAR __Max = MAXX(__Table,[period])
    VAR __Current = MAX('Table'[period])
RETURN
    IF(__Current = __Max,[distribution],BLANK())