Forum Discussion

tringuyenminh92's avatar
tringuyenminh92
Icon for Memorable Member rankMemorable Member
9 years ago
Solved

Pie chart showing wrong value for same measure when not filter date slicer

Hi all,

 

I got strange situation with calculated measure and pie chart/bar chart.  I have:

  1. Date slicer
  2. Calculated measure: (sum qty when there is fillter, if not, sum qty of the max date)
    sales amount = IF(ISFILTERED(FACT_SALES[Date]) , SUM(FACT_SALES[QTY]), 
CALCULATE(SUM(FACT_SALES[QTY]),FILTER(FACT_SALES, FACT_SALES[Date]= max(FACT_SALES[Date])  ) )	  )

When i choose one date in slicer, everything is working correctly. but when select nothing. the pie chart with SITEID in legend showing wrong value. however it's correct without siteid.

 

when choose one datewhen not choose date and have siteid in legend of pie chartthe pie chart in the right doesnt have siteid and it's showing correctly

 

Did i miss something in my DAX expression?

Data Modeling
Reports
  • v-haibl-msft's avatar
    v-haibl-msft
    9 years ago

    tringuyenminh92

     

    Please refer to following screenshot, if it is your desired result. Please try with following DAX expression.

     

    sales amount = 
VAR MaxDate =
    CALCULATE (
        MAX ( FACT_SALES[Date] ),
        ALLSELECTED ( FACT_SALES[SITEID] ),
        ALLSELECTED ( FACT_SALES[Date] )
    )
RETURN
    IF (
        ISFILTERED ( FACT_SALES[Date] ),
        SUM ( FACT_SALES[QTY] ),
        CALCULATE (
            SUM ( FACT_SALES[QTY] ),
            FILTER ( FACT_SALES, FACT_SALES[Date] = MaxDate )
        )
    )

     

     

    Best Regards,
    Herbert

2 Replies

  • v-haibl-msft's avatar
    v-haibl-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    tringuyenminh92

     

    Please refer to following screenshot, if it is your desired result. Please try with following DAX expression.

     

    sales amount = 
VAR MaxDate =
    CALCULATE (
        MAX ( FACT_SALES[Date] ),
        ALLSELECTED ( FACT_SALES[SITEID] ),
        ALLSELECTED ( FACT_SALES[Date] )
    )
RETURN
    IF (
        ISFILTERED ( FACT_SALES[Date] ),
        SUM ( FACT_SALES[QTY] ),
        CALCULATE (
            SUM ( FACT_SALES[QTY] ),
            FILTER ( FACT_SALES, FACT_SALES[Date] = MaxDate )
        )
    )

     

     

    Best Regards,
    Herbert

    • tringuyenminh92's avatar
      tringuyenminh92
      Icon for Memorable Member rankMemorable Member
      9 years ago

      Hi v-haibl-msft,

       

      Thanks for your response. Your suggestion is not my point, but I could refer your expr and realize that if i use variable, it will work for my expectation:

       

      Not working expr:

      m = IF(ISFILTERED(FACT_SALES[Ngày]) , SUM(FACT_SALES[QTY]), 
CALCULATE(SUM(FACT_SALES[QTY]),FILTER(all(FACT_SALES), FACT_SALES[Ngày]=CALCULATE( max(FACT_SALES[Ngày]),all(FACT_SALES) )  ) )	  )

      And working expr after refer your code:

      sales amount = 
VAR MaxDate =
    CALCULATE (
        MAX ( FACT_SALES[Ngày] ),
		ALL(FACT_SALES)
    )
RETURN
    IF (
        ISFILTERED ( FACT_SALES[Ngày] ),
        SUM ( FACT_SALES[QTY] ),
        CALCULATE (
            SUM ( FACT_SALES[QTY] ),
            FILTER ( FACT_SALES, FACT_SALES[Ngày] = MaxDate )
        )
    )

       

      v-haibl-msft: one more concern, is there any wrong with the old expression? (using all inside the filter method)