Dynamic Max date with slicer

[Anonymous]

Hello, 

I work on a issue that i have partially resolve. 

I want to count only 1 Revisions status by ID, The good status is the status of Max date in date slicer.

Actually, the max date is static and not dynamic relativ to date slicer.

I want to my mesure return the status relative to the max date in the date slicer. 

Somebody can help to achieve that ? 

You can see below my mesure, and my pbix. 

Measure =
VAR a =
    CALCULATE ( MAX ( 'Table'[Revision Date] ), ALLEXCEPT ( 'Table', 'Table'[ID] ) )
RETURN
    CALCULATE (
        MAX ( 'Table'[Revision Status] ),
        ALLEXCEPT ( 'Table', 'Table'[ID] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Revision Date] = a )
    )
Measure 2 =
IF ( [Measure] = MAX ( 'Table'[Revision Status] ), 1, 0 )

count_A =
VAR a =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER ( 'Table', [Measure 2] = 1 && 'Table'[Measure] = "A" )
    )
RETURN
    IF ( ISBLANK ( a ), 0, a )
count_B =
VAR b =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER ( 'Table', [Measure 2] = 1 && 'Table'[Measure] = "B" )
    )
RETURN
    IF ( ISBLANK ( b ), 0, b )

Count_c =
VAR c =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER ( 'Table', [Measure 2] = 1 && [Measure] = "C" )
    )
RETURN
    IF ( ISBLANK ( c ), 0, c )

Thanks by advance

[v-lili6-msft]

hi, @Anonymous 

If you could use this formula to add a measure

Measure = 
VAR a =
    CALCULATE ( MAX ( 'Feuil1'[Revision Date] ),FILTER(ALLSELECTED(Feuil1),Feuil1[ID]=MAX(Feuil1[ID])))
RETURn
    CALCULATE (
        MAX ( 'Feuil1'[Revision Status] ),
        ALLEXCEPT ( 'Feuil1', 'Feuil1'[ID] ),
        FILTER ( ALL( 'Feuil1'), 'Feuil1'[Revision Date] = a )
    )

If not your case, please share your expected output based on this sample report.

Best Regards,

Lin

[Anonymous]

Hello @v-lili6-msft,

Thanks for your anwer, i have done a screenshot that show the expected result with KPI. 

Let me know if you need more explaination. 

Thanks by advance 🙂 

[v-lili6-msft]

hi, @Anonymous 

When date is from 1/1/2018 to 6/7/2018, Why count_A is 1 not 2?

What is the logic of measure 2?

Measure 2 = 
IF ( [Measure] = CALCULATE(MAX ( 'Feuil1'[Revision Status] ),ALLSELECTED(Feuil1[Revision Date])), 1, 0 )

could you explain the expected output with the sample data?

Best Regards,

Lin

[Anonymous]

@v-lili6-msft 

---

When date is from 1/1/2018 to 6/7/2018, Why count_A is 1 not 2?

---

Because the status is the status of max date for each ID is 

A1 = A

A2 = C 

A3 = C 

A4 = B

So there is only 1 ID with A status. 

---

What is the logic of measure 2?

Measure 2 = 
IF ( [Measure] = CALCULATE(MAX ( 'Feuil1'[Revision Status] ),ALLSELECTED(Feuil1[Revision Date])), 1, 0 )

The logic is : 

Measure : must show the status of the max date for each ID (this actually dont work)

Measure 2 :  must test if the status of each line = to the status of max date (measure)

Count_A/B/C : Count the row for each status who respond to previous test measure. 

The final objective of this sequence, is to build a mesure who reponsd at these criteria : 

• Only 1 Status can be count for each ID
• The Status is the status of max date for each ID
• The mesure must be relative to a date slicer

Actually is work, but the status show by "Measure" isn't relative to date slicer. 

---

could you explain the expected output with the sample data?

---

I already explain with sample data. Real dataset is more complexe. 

But i can give you a real life use case. 

"imagine that you have a warranty problem with your phone. You send it to the warranty department and here's what happens internally:

The issue is entered in the system with an ID.
An issue is then treated by different collaborators, each time they go through a step he gets a status.

For example.
Issue: ID1 (broken screen)
- RevisionID 1  : Status = A (to be done)
- RevisionID 2 : Status = B (in progress)
- RevisionID 3 : Status = C (Done)

So, for BI purposes, the service manager needs to know the number of issues "to be done", the number of issues "In progress".....
But cause of revisionIDs have a chronological order, if we use a date slicer, the status is relative to the revision date."

Thanks for your time. 

I hope to help with my explanations

[v-lili6-msft · Accepted solution]

hi, @Anonymous 

Ok, For "Measure : must show the status of the max date for each ID (this actually dont work)"

You could use the formula as above

Measure = 
VAR a =
    CALCULATE ( MAX ( 'Feuil1'[Revision Date] ),FILTER(ALLSELECTED(Feuil1),Feuil1[ID]=MAX(Feuil1[ID])))
RETURn
    CALCULATE (
        MAX ( 'Feuil1'[Revision Status] ),
        ALLEXCEPT ( 'Feuil1', 'Feuil1'[ID] ),
        FILTER ( ALL( 'Feuil1'), 'Feuil1'[Revision Date] = a )
    )

and for "Count_A/B/C : Count the row for each status who respond to previous test measure. "

I think you need to use DISTINCTCOUNT instead of COUNTROWS in these three measure

count_A = 
VAR a =
    CALCULATE (
        DISTINCTCOUNT(  Feuil1[ID]),
        FILTER ( Feuil1, [Measure 2] = 1 && [Measure]= "A" )
    )
RETURN
    IF ( ISBLANK ( a ), 0, a )

count_B = 
VAR b =
    CALCULATE (
        DISTINCTCOUNT ( Feuil1[ID] ),
        FILTER (  Feuil1 , [Measure 2] = 1 &&  Feuil1 [Measure] = "B" )
    )
RETURN
    IF ( ISBLANK ( b ), 0, b )

Count_c = 
VAR c =
    CALCULATE (
        DISTINCTCOUNT ( Feuil1[ID] ),
        FILTER (Feuil1, [Measure 2] = 1 && [Measure] = "C" )
    )
RETURN
    IF ( ISBLANK ( c ),0, c )

and here is my demo pbix file, please try it.

Best Regards,

Lin