Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Dynamic Max date with slicer

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

Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    7 years ago

    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

5 Replies

  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

       

      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's avatar
        v-lili6-msft
        Community Support
        7 years ago

        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