Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Last visit filter depending on date (part II)

Hi,

 

So we come from here; https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Variable-column-depending-on-filters... 

 

The original need was to get a 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 A01/01/2023YES
Center B02/01/2023NO
Center C04/01/2023YES
Center D15/01/2023NO
Center B02/02/2023YES
Center B04/02/2023YES
Center A08/02/2023NO
Center C09/02/2023NO
Center D02/02/2023NO
Center A10/02/2023YES

 

 

The development provided by @v-yiruan-msft  is correct, but it only works when you want to get the detail of a Yes presence, or a No presence. When you want to see the details of YES and NO in a visual, it is not accurate for some reason.

 

The original development is based in three measures:

 

 

 

 

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

 

 

 

 

 

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] = "NO"
        ),
        [Date of visit]
    )

 

 

 

 

 

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

 

 

 

And I have tried removing the "&& [Presence of our product] = "NO"" part, but as you can see in the PBI attached here, it wont work when we use "presence of our product" column to describe the leyend. The correct number is "No"=232, but if we use the column to describe we get 242 instead. 

 

Mape94_0-1686566438930.png


I must be missing something. Ill give you the sample data here and de PBIX file:

https://drive.google.com/file/d/177di68NHMkjZ3-ovYjYq4jmirXY76YKq/view?usp=sharing 

 

https://docs.google.com/spreadsheets/d/1EMqQVraRaQrSxW02-RbgMV6XBa9nclSx/edit?usp=sharing&ouid=10280... 

 

Thanks a lot in advance!

 

2 REPLIES 2
v-yanjiang-msft
Community Support
Community Support

Hi @Anonymous ,

After long time research, I'm still not quite clear about your sample and purpose. As your description "I have tried removing the "&& [Presence of our product] = "NO"" part", but why not remove it, here's the result of remove the part.

vyanjiangmsft_0-1686728722721.png

Best regards,

Community Support Team_yanjiang

Anonymous
Not applicable

Hi v-yanjiang, thanks for your answer and I apologize if i didnt make myself clear.

 

The measure, as you stated above in your picture, works, but it is not correct. For instance, the correct number of "No"s is 232 as I showed in my sample, not 242. Also, if you check your numbers in the table, they dont add up 242+215+2 is not 445. Correct number is NO= 232 + YES = 211 BLANK=2, and that is 445!

Hope I cleared it out. 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.