Forum Discussion

DidacBF's avatar
DidacBF
Frequent Visitor
4 years ago
Solved

Filtering data based on dynamic segmentations (measure)

Hello guys, first of all sorry for my English, I will try to explain what I want to achieve the best I can.
Not sure the Subject is very clear...

I reduced my model so I can show you easily what is my problem:


The user wants to see points of sale segmented dynamically based on two characteristics, number of orders (inside a period of time) and date of the last order. The user has a date slicer whitch filters de maximum calendar date and then it calculates for each Pont of sale its Segment (A,B,C,D...)

To do that I created this measures (code at the end):

Período:
Based on calendar dates
Período 0 = Current month until max date
Período 1 = Current month -1
Período 2 = Current month -2
Período 3 = Dates between Current month -3 and Current month -6

Segmento POS:
Classifies each POS based on the number of sales during each period (A,B,C,D...)

If I'm not mistaken, I can't use calculated tables or columns since everything needs to be dynamic, so I calculate the measures and then I use them in tables to filter data.
Everything seems to work fine, I can write measures to calculate number of POS, number of order sales, avg of SKUs, etc and shows the expected result.

The only thing I can't manage to do is filter a concrete table to show the detail of Order sales for each POS inside X segment.

What I do is calculate the number of Points Of Sale which had New orders (orders in period 0, current month), then I filter those POS for the segment A (for example, x number of orders in x period of time).

 

 

 

 

 

 

Number of POS with new orders = 
CALCULATE (
    COUNTROWS (
        FILTER(
            ADDCOLUMNS(
                GROUPBY('Point of sale','Point of sale'[NIF]),
                "Pedidos", CALCULATE(DISTINCTCOUNT('Order sale'[Sales order]), FILTER('Calendar',[Período]="Período 0"))
            ),[Pedidos]>0
        )
    )
)

Number of POS with new orders - A = 
CALCULATE(
    [Number of POS with new orders], FILTER('Point of sale','Point of sale'[Segmento POS]="A")
)

 

 

 


Then here you can see the results, as I say everything seems to work ok except when I using sales order (order ID). I guess it has to do with distinct counting those Order Sale (ID) to segment the POS but I can't find the answer...

 

Edit: I can't use Date either, so it's getting obvious the problem is because I'm doing something wrong with the dynamic segmentation, but I don't know what.

 

Dynamic measures:

 

 

Período = 
VAR inicio_periodo_0 = 
DATE(
    YEAR(CALCULATE(MAX(Calendar[Date]), ALLSELECTED('Calendar'))),
    MONTH(CALCULATE(MAX(Calendar[Date]), ALLSELECTED('Calendar'))),
    1
)

VAR final_periodo_0 = 
CALCULATE(
    MAX(Calendar[Date]), 
    ALLSELECTED('Calendar')
)

VAR inicio_periodo_1 =
DATE(
    YEAR(CALCULATE(MAX(Calendar[Date]), ALLSELECTED('Calendar'))),
    MONTH(CALCULATE(MAX(Calendar[Date]), ALLSELECTED('Calendar')))-1,
    1
)

VAR final_periodo_1 =
EOMONTH(
    CALCULATE(
    MAX(Calendar[Date]), 
    ALLSELECTED('Calendar')),
    -1
)

VAR inicio_periodo_2 =
DATE(
    YEAR(CALCULATE(MAX(Calendar[Date]), ALLSELECTED('Calendar'))),
    MONTH(CALCULATE(MAX(Calendar[Date]), ALLSELECTED('Calendar')))-2,
    1
)

VAR final_periodo_2 =
EOMONTH(
    CALCULATE(
    MAX(Calendar[Date]), 
    ALLSELECTED('Calendar')),
    -2
)

VAR inicio_periodo_3 =
DATE(
    YEAR(CALCULATE(MAX(Calendar[Date]), ALLSELECTED('Calendar'))),
    MONTH(CALCULATE(MAX(Calendar[Date]), ALLSELECTED('Calendar')))-6,
    1
)

VAR final_periodo_3 =
EOMONTH(
    CALCULATE(
    MAX(Calendar[Date]), 
    ALLSELECTED('Calendar')),
    -3
)

RETURN

IF(
    SELECTEDVALUE(Calendar[Date], BLANK())>=inicio_periodo_0 && SELECTEDVALUE(Calendar[Date], BLANK())<=final_periodo_0,
    "Período 0",
    IF(
        SELECTEDVALUE(Calendar[Date], BLANK())>=inicio_periodo_1 && SELECTEDVALUE(Calendar[Date], BLANK())<=final_periodo_1,
        "Período 1",
        IF(
            SELECTEDVALUE(Calendar[Date], BLANK())>=inicio_periodo_2 && SELECTEDVALUE(Calendar[Date], BLANK())<=final_periodo_2,
            "Período 2",
            IF(
                SELECTEDVALUE(Calendar[Date], BLANK())>=inicio_periodo_3 && SELECTEDVALUE(Calendar[Date], BLANK())<=final_periodo_3,
                "Período 3",
                BLANK()
            )
        )
    )
)
Segmento POS = 
VAR pedidos_periodo_1 = CALCULATE(DISTINCTCOUNT('Order sale'[Sales order]), FILTER('Calendar',[Período]="Período 1"))
VAR pedidos_periodo_2 = CALCULATE(DISTINCTCOUNT('Order sale'[Sales order]), FILTER('Calendar',[Período]="Período 2"))
VAR pedidos_periodo_3 = CALCULATE(DISTINCTCOUNT('Order sale'[Sales order]), FILTER('Calendar',[Período]="Período 3"))

RETURN
IF(pedidos_periodo_1=1, "G",
    IF(pedidos_periodo_1=2, "D",
        IF(pedidos_periodo_1>2, "A",
            IF(pedidos_periodo_2=1, "H",
                IF(pedidos_periodo_2=2, "E",
                    IF(pedidos_periodo_2>2, "B",
                        IF(pedidos_periodo_3=1, "I",
                            IF(pedidos_periodo_3=2, "F",
                                IF(pedidos_periodo_3>2, "C", BLANK()
                                )
                            )
                        )
                    )
                )
            )
        )
    )
)

 

 

 

There are for sure easier solutions but I coulnd't find them. I hope I made myself clear and you can understand what I'm trying to do and what my problem is.

Thank you!!!


If you need it I can prepare a PBIX with sample data and everything.


Edit:
I thinl everything works fine now changing the measure that calcultes the segmentation of each POS, added ALL(Orders[ID ORDER])

Segmento farmacia = 
VAR pedidos_periodo_1 = CALCULATE(DISTINCTCOUNT(Pedidos[No. Orden]), ALL(Pedidos[No. Orden]), FILTER(Calendario,[Período]="Período 1"))
VAR pedidos_periodo_2 = CALCULATE(DISTINCTCOUNT(Pedidos[No. Orden]), ALL(Pedidos[No. Orden]), FILTER(Calendario,[Período]="Período 2"))
VAR pedidos_periodo_3 = CALCULATE(DISTINCTCOUNT(Pedidos[No. Orden]), ALL(Pedidos[No. Orden]), FILTER(Calendario,[Período]="Período 3"))



  • i think you are on the right track. You cannot count measure results. You need to convert measure groups to (temporary) tables before you can do the segment assignment. This means you need to use completely different measures to compute these segmentations.  Don't expect the results to be flexible - they will be highly specialized to work in a very narrow UI band. The moment your customer wants you to add "just one more small filter"  you may have to redo everything.

     

    Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to work with. Please show the expected UX outcome.

2 Replies

  • i think you are on the right track. You cannot count measure results. You need to convert measure groups to (temporary) tables before you can do the segment assignment. This means you need to use completely different measures to compute these segmentations.  Don't expect the results to be flexible - they will be highly specialized to work in a very narrow UI band. The moment your customer wants you to add "just one more small filter"  you may have to redo everything.

     

    Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to work with. Please show the expected UX outcome.

  • DidacBF's avatar
    DidacBF
    Frequent Visitor

    Hi lbendlin !


    Using ALL() in the segmentation mesure solved all my problems. I feel that by following the trace of the error I usually end up finding the solution, but I don't fully (not even close) understand what is happening behind it :D. 

    As you say, this report only serves to show that very specific data, but it was the idea from the beginning.

    Thank you for taking the time to read it!!