Variable column depending on filters

[Mape94]

Hi,

I want to calculate statuses on columns depending on filters applied in the model. The reason why I cannot make it in measures is because it wouldnt work in the segmentation visual.

Here are two examples of what I need:

Verify if a line of info contains the last date of visit of each name of store. If it is the last date then yes else no. This will change if we select different dates, so I would want the expression to be evaluated everytime I select different filters (not only date but all filters in the model).

Evalutate clusters: Make it Client A if it has sold more than XXX$ in the last 5 months, B if has at least one purchase order in the last month and has not sold more than XXX$ in the last 5 months. Again, this will be different depending on the date selected.

I would appreciate some help, thank you all in advance!!

[v-yiruan-msft]

Hi @Mape94 ,

According to your description, it seems that you want to create a calculated column with the statuses and the value will be changed dynamically base on filters. But the calculated column is computed during data refresh and uses the current row as a context, it will not change according to the user interaction(slicer, filter, column selections etc.) in the report... You need to create a measure to replace it. Please review the following links about the difference of calculated column and measure.

Calculated Columns and Measures in DAX

Calculated Columns vs Measures

In addition, you can provide some raw data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. Later I will check it and check if there is any workaround to achieve the same requirement. You can refer the following links to share the required info:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[Mape94]

Hi,

I suspected it wont be possible to adress this question with calculated columns, but wasnt 100% aware of how they work so thanks a lot.

Now, lets try a workaround with measures then. Ideally I would need them to work in the segmentation visual, so the final data viewers could filter Cluster and Last visit according to their needs, but the closest possible to this would help as well.

I have provided sample raw data here: https://we.tl/t-mPMX9p69gP 

I have no idea where to start in measures to calculate this, but I will try to be specific in what to expect:

Last visit filter: Normally we evaluate the presence of our products in a number of shops. So, it is usefull to have the number of centers with our products in the last date we checked. I could solve it making a measure that selects lastdate of each center, but what I need is the possibility to have the same visuals calculating presence-related measures and a possibility to filter "Is it last visit? Yes/No, this will depend on date selected.

example: if we select all the dates of the following table, Presence with "Is it last visit?= Yes" will add up to 2. Contrary to that, "Is it last visit= Yes and No" filter, the result will be 5.

Shop//Date of visit(DD/MM/YYYY)//Presence of our product

Center A	01/01/2023	YES
Center B	02/01/2023	NO
Center C	04/01/2023	YES
Center D	15/01/2023	NO
Center B	02/02/2023	YES
Center B	04/02/2023	YES
Center A	08/02/2023	NO
Center C	09/02/2023	NO
Center D	02/02/2023	NO
Center A	10/02/2023	YES

The problem is similar in the second case I explain to evaluate Clusters. A segmentation visual will be helpful to filter by cluster, but in time, cluster changes according to conditions set in the model, so I would love to evaluate this depending, most of all, on date.

Let me know if more details are needed, and thanks in advance!

[v-yiruan-msft]

Hi @Mape94 ,

As checked the sample data and pbix file which you provided, I'm not very clear about your requirement. Do you want to get the count of shop which satisfy the following requirement?

• [Presence of our product] is "Yes"
• Is it last visit?= Yes or No
• In selected date period

But how can we identify whether that is last visit or not? And could you please explain why the result will be 2 when  "Is it last visit?= Yes" base on the below sample data?

example: if we select all the dates of the following table, Presence with "Is it last visit?= Yes" will add up to 2. Contrary to that, "Is it last visit= Yes and No" filter, the result will be 5.

Shop//Date of visit(DD/MM/YYYY)//Presence of our product

Center A	01/01/2023	YES
Center B	02/01/2023	NO
Center C	04/01/2023	YES
Center D	15/01/2023	NO
Center B	02/02/2023	YES
Center B	04/02/2023	YES
Center A	08/02/2023	NO
Center C	09/02/2023	NO
Center D	02/02/2023	NO
Center A	10/02/2023	YES

---

Best Regards

[Mape94]

Hi,

Last visit stands for last visit to a specific center. For instance, if date threshold is from 01/01/2023 to 10/02/2023, lines with last visit will be:

Shop//Date of visit(DD/MM/YYYY)//Presence of our product // is it last visit?

Center A	01/01/2023	YES	No last
Center B	02/01/2023	NO	No last
Center C	04/01/2023	YES	No last
Center D	15/01/2023	NO	No last
Center B	02/02/2023	YES	No last
Center B	04/02/2023	YES	Last
Center A	08/02/2023	NO	No last
Center C	09/02/2023	NO	Last
Center D	02/02/2023	NO	Last
Center A	10/02/2023	YES	Last

just to clear this, if we selected date from 01/01/2023 to 31/01/2023, the resulting table would be:

Shop//Date of visit(DD/MM/YYYY)//Presence of our product // is it last visit?

Center A	01/01/2023	YES	Last
Center B	02/01/2023	NO	Last
Center C	04/01/2023	YES	Last
Center D	15/01/2023	NO	Last

Hope it clarifies,

Thanks

[v-yiruan-msft · Accepted solution]

Hi @Mape94 ,

I updated your sample pbix file(see the attachment), please find the details in new created page [Page 2]. You can follow the steps below to get it:

1. Update the formula of measure [Last visit filter] as below

Last visit filter = 
VAR _mindate =
    MIN ( 'CALENDAR'[Date] )
VAR _maxdate =
    MAX ( 'CALENDAR'[Date] )
VAR _selshop =
    SELECTEDVALUE ( 'Date of visit'[Shop] )
VAR _selvisitdate =
    SELECTEDVALUE ( 'Date of visit'[Date of visit] )
VAR _lastvisitdate =
    CALCULATE (
        MAX ( 'Date of visit'[Date of visit] ),
        FILTER ( ALLSELECTED ( 'Date of visit' ), 'Date of visit'[Shop] = _selshop )
    )
RETURN
    IF (
        _selvisitdate < _mindate
            || _selvisitdate > _maxdate,
        BLANK (),
        IF (
            _selvisitdate = _lastvisitdate
                && _selvisitdate >= _mindate
                && _selvisitdate <= _maxdate,
            "Y",
            "N"
        )
    )

2. Create a dimension table as below

3. Update the formula of measure [Presence Yes] as below

Presence Yes = 
VAR _filter = [Last visit filter]
VAR _tab =
    SUMMARIZE (
        'Date of visit',
        'Date of visit'[Shop],
        'Date of visit'[Date of visit],
        'Date of visit'[Presence of our product],
        "@lastvisit", _filter
    )
RETURN
    COUNTX (
        FILTER (
            _tab,
            [@lastvisit]
                IN ALLSELECTED ( 'Last Visit'[Last Visit] )
                    && [Presence of our product] = "Yes"
        ),
        [Date of visit]
    )

4. Create a measure as below and put it onto the column chart to replace the measure [Presence Yes]

Measure = 
SUMX (
    GROUPBY (
        'Date of visit',
        'Date of visit'[Shop],
        'Date of visit'[Date of visit]
    ),
    [Presence Yes]
)

Best Regards

[Mape94]

Thanks, it worked perfectly