Dynamic Calculated Column based on Slicer

[Anonymous]

I am trying to update a value in a calculated column using a slicer selection, to allow the user to see the effect of allowing a greater time to time to pay.

I have set up a seperate table containing the values for the slicer that is not linked and I am using the below statement 

Segmentation =

IF('FinalImport'[Clearing Date] = BLANK(), "3.Poor Payers",

       IF(FinalImport'[DAYS TO PAY] <= 0 , "1.Good Payers - On Time",

                IF([paysegcheck] = 0, "2.Average Payers",

                       IF([paysegcheck] = 1 , "3.Poor Payers"))))

Pay seg check is measure which updates to a 1 or 0 based on minusing the slicer value from another field, I can see this works fine. However the Segmentation column is not updating? Can anyone help with where I am going wrong?

[sturlaws]

Hi,

it is not possible to update a calculated column from slicer selection, I'm afraid. You would have to handle it as a measure.

cheers,

S

[Anonymous]

Thanks is there any workaround for this?

[sturlaws]

yes, but it depends on what you want to achive. Do you want a list of customers and which category they are in, or the count of customers pr category? Or filter on a category?

[Anonymous]

I want to be able to see the count of customer in each category and then use the category as a filter.

[v-lid-msft]

Hi @Anonymous ,

The measure is dynamic but the calculated columns are computed during the database processing and then stored in the model. So In your scenario, we cannot make the calculated column dynamically.

If you want to give customer a type depends on another measure, you can try the following steps.

Create a table contain all the types

create a measure to count by type

TypeCount =
COUNTROWS (
    FILTER (
        ADDCOLUMNS (
            ALL ( FinalImport ),
            "Type", IF (
                'FinalImport'[Clearing Date] = BLANK (),
                "3.Poor Payers",
                IF (
                    [DAYS TO PAY] <= 0,
                    "1.Good Payers - On Time",
                    IF (
                        [paysegcheck] = 0,
                        "2.Average Payers",
                        IF ( [paysegcheck] = 1, "3.Poor Payers" )
                    )
                )
            )
        ),
        [Type] = SELECTEDVALUE ( TypeTable[Type] )
    )
)

The "check" slicer control the measure paysegcheck 

BTW, pbix as attached.

Best regards,

Community Support Team _ DongLi
If this post helps, then please consider Accept it as the solution to help the other members find it more 

[sturlaws]

@Anonymous,

out of curiosity, what does the code for you measure [paysegcheck] look like?

[Anonymous]

I think this is also part of my problem, I need to consider the days to pay for each order seperately, so I think my calculation below is probably incorrect paysegcheck = IF(SUM(FinalImport[DAYS TO PAY]) - [Interval Value] <0, 1, 0)

[sturlaws]

When using a measure in the FILTER-function, https://dax.guide/filter/, the measure is evaluated for each row in the table which is filtered. So [paysegcheck] is calculated for each row, if that was what you were worried about.

[Anonymous]

so if I wanted to just show on a chart two lines one for actual days to pay and one for days to pay - my slicer value, what would be the best way to do this? 

[sturlaws]

If you have a separate table,Categories, with 1 column,Category,  and Poor, Average and Good on the rows, create a measure like this:

countPrCategory =
SWITCH (
    TRUE ();
    SELECTEDVALUE ( 'Categories'[Category] ) = "Poor"; CALCULATE (
        DISTINCTCOUNT ( Table[customerID] );
        FILTER ( Table; ISBLANK ( Table[clearing date] ) || [paysegcheck] = 1 )
    );
    SELECTEDVALUE ( 'Categories'[Category] ) = "Average"; CALCULATE (
        DISTINCTCOUNT ( Table[customerID] );
        FILTER ( Table; [paysegcheck] = 0 )
    );
    SELECTEDVALUE ( 'Categories'[Category] ) = "Good"; CALCULATE (
        DISTINCTCOUNT ( Table[customerID] );
        FILTER ( Table; [days to pay] <= 0 )
    );
    0
)

If you also want to filter on these categories, add this measure to the filter part of the visual(s), and set it to greater than 0