Forum Discussion

lokosrio's avatar
lokosrio
Icon for Helper II rankHelper II
5 years ago

Filter items which are not max id

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_idlast_visit_idperioddistribution
prod_1lv_12021P580%
prod_2lv_12021P560%
prod_3lv_32021P1140%
prod_4lv_32021P1120%
prod_5lv_32021P1190%

 

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_idlast_visit_idperioddistribution
prod_1lv_12021P580%
prod_2lv_12021P560%

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

prod_3lv_32021P1140%
prod_4lv_32021P1120%
prod_5lv_32021P1190%

 

Could you please help me with this?

Need Help

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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())

     

  • lokosrio's avatar
    lokosrio
    Icon for Helper II rankHelper II
    5 years ago

    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_idlast_visit_idperioddistribution
    prod_1lv_12021P5 
    prod_2lv_12021P5 
    prod_3lv_32021P11100%
    prod_3lv_42021P110%

    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_iddistribution
    prod_1 
    prod_2 
    prod_350%

    Could you please help me with this?

    • Greg_Deckler's avatar
      Greg_Deckler
      Icon for Community Champion rankCommunity Champion
      5 years ago

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

  • v-xiaotang's avatar
    v-xiaotang
    Icon for Community Support rankCommunity Support
    5 years ago

    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.