Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Member | Transaction Date | Status |
JB | Monday, January 4, 2021 | New Join |
DC | Sunday, January 10, 2021 | New Join |
JB | Saturday, February 6, 2021 | Cancel |
JB | Monday, February 8, 2021 | Reinstate |
DC | Wednesday, February 10, 2021 | Cancel |
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
Solved! Go to Solution.
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"
)
This is my PBIX file.
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 : 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
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
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
)
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
@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.
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)
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.
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.
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 :
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"
)
This is my PBIX file.
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.
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])
)
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
That logic is okay. However, the total seems to be incorrect.
This result would be incorrect if we use this measure in a card.
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
)
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
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
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |