Forum Discussion

Anonymous

Not applicable

3 years ago

Solved

Variable column depending on filters

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 ...

Variable column

Anonymous

3 years ago

Hi Anonymous ,

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

Sample last visit and cluster(Updated).pbix72 KB

Anonymous

Not applicable

3 years ago

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!

Anonymous

Not applicable

3 years ago

Hi Anonymous ,

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

Anonymous

Not applicable

3 years ago

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

Anonymous

Not applicable

3 years ago

Hi Anonymous ,

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

Sample last visit and cluster(Updated).pbix72 KB

Anonymous
Not applicable
3 years ago
Thanks, it worked perfectly

Forum Discussion

Variable column depending on filters

Related content

Dependent Filters and Filter Depend on Slicer

Filter dependencies

Filter depending on another filter

Circular dependency

Variable still filtered in visual where using REMOVEFILTERS