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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
JIGAR
Resolver IV
Resolver IV

Calculate DistinctCounts based on Max Date within the selected date range

Hello, 

 

I am trying to calculate a measure to count the distinct number of new joins, cancels and reinstates based on the max transaction date.

 

Here is the sample data. 

 

Fact Table 

MemberTransaction DateStatus
JBMonday, January 4, 2021New Join
DCSunday, January 10, 2021New Join
JBSaturday, February 6, 2021Cancel
JBMonday, February 8, 2021Reinstate
DCWednesday, February 10, 2021Cancel

 

Fact table is connected to Dim Date table 

 

What I am trying to achieve is 

 

For example : 

 

If I select a date range from Jan 1 2021 to Jan 5 2021

 

New Joins (#) = 1 (Only JB has new join status)

Cancel (#) = 0 (None of the member has cancel status)

Reinstate (#) = 0 (None of the member has reinstate status)

 

If I select date range from Jan 1 2021 to Jan 31 2021

 

New Joins (#) = 2 (JB and DC both have new join status)

Cancel (#) = 0 (None of the member has cancel status)

Reinstate (#) = 0 (None of the member has reinstate status)

 

If I select date range from Jan 1 2021 to Feb 7 2021

 

New Joins (#) = 1 ( This is member DC and not JB)

Cancel (#) = 1 (Since JB now is on cancel status)

Reinstate (#) = 0 (None of the member has reinstate status)

 

If I select date range from Jan 1 2021 to Feb 28 2021

 

New Joins (#) = 0 (None of the member is on new join status now)

Cancel (#) = 1 (Since DC now is on cancel status)

Reinstate (#) = 1 (Since JB now is on reinstate status)

 

I hope I was able to explain the question.

 

Any help here is greatly appreciated. 

 

Thank you

3 ACCEPTED SOLUTIONS

Hi @JIGAR ,

Try the following formula:

Result = 
var _Table = 
    FILTER(
        ALLSELECTED('Fact Table'), 
        'Fact Table'[Multiplier] = 1 
        && 'Fact Table'[Member] = MAX('Fact Table'[Member]) 
    )
var _Date = 
    MINX(
        _Table,
        'Fact Table'[Transaction Date]
    )
return 
    COALESCE(
        CALCULATE(
            MAX('Fact Table'[Status]),
            FILTER(
                _Table,
                'Fact Table'[Transaction Date] = _Date
            )
        ),
        "NA"
    )

v-kkf-msft_0-1614661824291.png

This is my PBIX file.

https://microsoftapc-my.sharepoint.com/:u:/g/personal/v-lazhang_microsoft_com/EefZ93NSdIZOrajS29WnBw...

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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

View solution in original post

@v-kkf-msft : This thing worked really well. Thank you very much.

 

However, how do I calculate the count of members on each status. 

 

So going with the same example above : 

 

For this date range and result 

 

JIGAR_0-1619805642071.png

How can I show the count 

 

Reinstate = 1

New Join = 0

Cancel = 0

 

This count should change based on the date range selected and also retaining the above logic. 

 

Can you please help me with that DAX logic ? 

 

Thank you 

View solution in original post

Hi @JIGAR ,

 

Try the following formula:

Measure = 
var _count=
    IF(
        MAX('Fact Table'[Status]) = [Result],
        COUNT('Fact Table'[Multiplier])
    )
var total = 
    CALCULATE(
        DISTINCTCOUNT('Fact Table'[Member]),
        FILTER(
            'Fact Table',
            [Result] <> "NA"
        )
    )
return 
    IF(
        ISFILTERED('Fact Table'[Status]),
        _count,
        total
    )

image.png

 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

View solution in original post

11 REPLIES 11
amitchandak
Super User
Super User

@JIGAR , Create a measure like

Measure 7 = Sumx(SUMMARIZE(Table3,Table3[Member],"_1",if(CALCULATE(max(Table3[Status]), filter(Table3, Table3[Transaction Date] =CALCULATE(max(Table3[Transaction Date]), filter(ALLSELECTED(Table3), Table3[Member] =max(Table3[Member]))))) ="New Join",1,0)),[_1])

 

Thank you so much @amitchandak 

 

This logic worked perfectly okay until I tried to plot these measures on a line chart/column chart. 

 

While plotting these measures on a line chart, its double counting the members. 

 

For a date range 1 Jan 2021 to 28 Feb 2021, 

 

JB is now in reinstate state

DC is now in cancel state. 

 

The screenshot below shows that there are 2 members in New Joins and 1 each in reinstate and cancel. 

 

JIGAR_0-1613490603651.png

 

The KPI cards on the tops shows the correct numbers. 

 

Any idea how do I handle this ? 

 

Thank you again for your help 😊

Hi @JIGAR ,

Try the following formula to create two measures:

RecentDate = 
CALCULATE(
  MAX( 'Fact Table'[Transaction Date] ), 
  FILTER( 
    ALLSELECTED( 'Fact Table' ), 
    'Fact Table'[Member] = MAX( 'Fact Table'[Member] )
  )
)
MemberCount = 
VAR MemberSegment = 
    FILTER(
        ALLSELECTED('Fact Table'),
        VAR NewDate = [RecentDate]
        VAR SegmentForMember = 
            FILTER (
                'Fact Table',
                'Fact Table'[Member] = 'Fact Table'[Member]
                &&'Fact Table'[Transaction Date] = NewDate
            )
        VAR IsMemberInSegments = NOT ISEMPTY(SegmentForMember) 
        RETURN IsMemberInSegments 
    )
VAR Result = 
    CALCULATE (
        COUNTROWS( 'Fact Table' ),
        KEEPFILTERS(MemberSegment)
    )
RETURN COALESCE(Result,0)

 image.png

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

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

@v-kkf-msft : 

 

Apologies for the delayed response. This logic worked correctly when I tried to plot it on the column chart. Thank you so much for the measure formula.

 

However, the table shows the total member count as 5 (see screenshot). It should show 2 as shown in the adjacent measure. 

JIGAR_2-1614632027935.png

 

 

My requirement has changed a little bit here. 

 

I want to show the first positive status for each member. i.e. - Status based on the minimum transaction date where Multiplier value is +1


For e.g. 

 

For Date Range 1/1/2021 to 2/28/2021

 

JB - New Join

DC - New Join

 

For date range 2/1/2021 to 2/28/2021

 

JB - ReInstate

DC - NA

 

Hope that explains the situation. Can you please help on how to handle this scenario ? 

 

Please see the new format of data attached below :

JIGAR_1-1614631945006.png

 

Thank you very much

 

Regards

Hi @JIGAR ,

Try the following formula:

Result = 
var _Table = 
    FILTER(
        ALLSELECTED('Fact Table'), 
        'Fact Table'[Multiplier] = 1 
        && 'Fact Table'[Member] = MAX('Fact Table'[Member]) 
    )
var _Date = 
    MINX(
        _Table,
        'Fact Table'[Transaction Date]
    )
return 
    COALESCE(
        CALCULATE(
            MAX('Fact Table'[Status]),
            FILTER(
                _Table,
                'Fact Table'[Transaction Date] = _Date
            )
        ),
        "NA"
    )

v-kkf-msft_0-1614661824291.png

This is my PBIX file.

https://microsoftapc-my.sharepoint.com/:u:/g/personal/v-lazhang_microsoft_com/EefZ93NSdIZOrajS29WnBw...

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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

@v-kkf-msft : 

 

Can you please help me with the measure to show the counts for each status ? 

 

Thank you

Hi @JIGAR ,

 

Try the following formula:

 

Measure = 
IF(
    MAX('Fact Table'[Status]) = [Result],
    COUNT('Fact Table'[Multiplier])
)

image.png

 

 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

@v-kkf-msft : 

 

That logic is okay. However, the total seems to be incorrect. 

 

This result would be incorrect if we use this measure in a card.

 

JIGAR_0-1620191590335.png

 

Hi @JIGAR ,

 

Try the following formula:

Measure = 
var _count=
    IF(
        MAX('Fact Table'[Status]) = [Result],
        COUNT('Fact Table'[Multiplier])
    )
var total = 
    CALCULATE(
        DISTINCTCOUNT('Fact Table'[Member]),
        FILTER(
            'Fact Table',
            [Result] <> "NA"
        )
    )
return 
    IF(
        ISFILTERED('Fact Table'[Status]),
        _count,
        total
    )

image.png

 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

@v-kkf-msft : That thing worked really well.

 

Thank you very much for your help !! Highly appreciated 😊

@v-kkf-msft : This thing worked really well. Thank you very much.

 

However, how do I calculate the count of members on each status. 

 

So going with the same example above : 

 

For this date range and result 

 

JIGAR_0-1619805642071.png

How can I show the count 

 

Reinstate = 1

New Join = 0

Cancel = 0

 

This count should change based on the date range selected and also retaining the above logic. 

 

Can you please help me with that DAX logic ? 

 

Thank you 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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