2 seperate filters for 1st instance of value in a row and all other instances of that value in a row

[Flawn]

Hello All,

I find this one very hard to describe directly so will instead go through the design I have been asked to achieveas it was presented to me:

We have clients that can have multiple Sessions associated with them. One of the visuals on the dashboard should what percentage of our clients have more than two sessions associated with them - and this should all be adjustable with a date-of-session filter: this has all been achieved.

Now here's the confounding part: The *first* session a client has should always be counted as a part of this percentage calculation - regardless of how the filter has been applied on the dashboard. 

So from my understanding the actual design request is as follows: "Create a dashboard visual that shows the percentage of clients with at least one session in the date-of-session-filter's given timeline and at least one *other* session that has *ever* occcured - ignoring the date-of-session-filter's given timeframe."

I hope this description makes any degree of sense - as I have no idea how to do this one at all.

For the sake of the request there are effectively 2 columns on one table a: Client ID & Session ID, and 2 columns on table b: Session ID and Date-of-Session

Thank you very much for any help you can provide! I'm happy to elaborate as needed.

[Anonymous]

Hi @Flawn ,
Here some steps that I want to share, you can check them if they suitable for your requirement.
Here is my test data:
Table A

Table B

Create a time dimension table

Table = VALUES('Table B'[Session Date])

Ensure a relationship between two tables with [Session ID]

Create measures

First Date = 
CALCULATE(
    MIN('Table B'[Session Date]),
    FILTER(
        ALLEXCEPT(
            'Table A',
            'Table A'[Customer ID]
        ),
        SELECTEDVALUE('Table B'[Session Date]) >= MIN('Table'[Session Date]) &&
        SELECTEDVALUE('Table B'[Session Date]) <= MAX('Table'[Session Date])
    )
)

Percentage of owned First date = 
VAR a = DISTINCTCOUNT('Table A'[Customer ID])
VAR b = 
CALCULATE(
    DISTINCTCOUNT('Table A'[Customer ID]),
    FILTER(
        'Table B',
        'Table B'[Session Date] = [First Date]
    )
)
RETURN
b/a

Count rows by customer id = 
VAR a = 
CALCULATE(
    COUNT('Table A'[Customer ID]),
    FILTER(
        ALLEXCEPT(
            'Table A',
            'Table A'[Customer ID]
        ),
        RELATED('Table B'[Session Date]) <= MAX('Table'[Session Date]) && 
        RELATED('Table B'[Session Date]) >= MIN('Table'[Session Date])
    )
)
RETURN
IF(
    a >= 2,
    1,
    0
)

Count over 2 = 
VAR _table = 
SUMMARIZE(
    'Table A',
    'Table A'[Customer ID],
    "Count",[Count rows by customer id]
)
RETURN
SUMX(_table,[Count])

Percentage of count ID > 2 = [Count over 2] / DISTINCTCOUNT('Table A'[Customer ID])

Final output

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Flawn]

Hi @Anonymous .

While this looks excellent, it unfortunately does not produce the results we would expect. When we use these formula both "percentage of owned first date"  and "percentage of count ID >2" produces an output of 100%. When we calculate a sample of the data manually we would be expecting a value of a bout 38% for the percentage of count ID >2..

[Anonymous]

Hi, @Flawn ,
Please provide complete example data and expected results so we can help you faster. Please hide sensitive information in advance.

Best regards,
Albert He

[Flawn]

Sure thing! Seems obvious you'd need that in retrospect.

Sample Data 

This sample data is based off one of the programs in our org for which we have a fairly confident approximation of the numbers we should be expecting.

The first page is the data for the current half of the FY, and the second page is that program's entire relevent data history.

We are expecting that approximately 38% of the clients on the first page (89 clients) should have at least two sessions across both the first page and the second page - with at *least* one of those sessions being from the first page. So theoretically a client with 20 sessions on page one, and 0 sessions on page 2 should be counted - but not the other way around.

Let me know if you need any more info! I really appreciate the help. 

[Anonymous]

Hi @Flawn ，
Thanks for your reply, unfortunately we are not able to open the example data you shared, if you can, you can provide the example data as described in the link below. Or provide your pbix file.
 https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
How to Get Your Question Answered Quickly - Microsoft Fabric Community

Best regards,
Albert He

[Flawn]

Hi @Anonymous 

I thought I had! But I guess it needs to be linked as a file, not a google sheet.

Either way, here's the data in a hopefully more acceptable format:

https://drive.google.com/file/d/1MKVkSu2D7HJLq3FoGMxcOrRv_oNl88LK/view?usp=sharing

Warm Regards,
Flawn