Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Slicer/Filter is eliminating data in chart

I'm resurrecting this issue, as my band-aid solution is not adequate. I have a table visualization where I have forecasted future sales. I created a slicer to filter out the data by differing products. When I click on a product, the forecast data (column PROJECTED GRWTH2) disappears. I believe I need to add something to DAX but not sure what that would be. DAX and image below: 

 

PROJECTED GRWTH2 =
VAR vCurYear =
CALCULATE ( YEAR ( MAX ( 'Deep Dive'[Process Date] ) ), ALL('Deep Dive') )
VAR vCurYearSales =
CALCULATE([ACV+YTD Est.DD], ('Calendar Table'[Year] = vCurYear))
VAR vLastYearSales =
CALCULATE ([ACV+YTD Est.DD], 'Calendar Table'[Year] = vCurYear - 1 )
VAR vGrowthRate =
DIVIDE ( vCurYearSales - vLastYearSales, vLastYearSales )
VAR vYearIncrement =
MAX ( 'Calendar Table'[Year] ) - vCurYear
VAR vProjGrowth =
vCurYearSales
* POWER ( 1 + vGrowthRate, vYearIncrement )
VAR vResult =
IF ( MAX ( 'Calendar Table'[Year] ) <= vCurYear, BLANK (), ROUND ( vProjGrowth, 0 ) )
RETURN
vResult
 

 

Need Help

3 Replies

  • v-luwang-msft's avatar
    v-luwang-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi  Anonymous ，

    Could you pls share your pbix file ?Remember to remove confident data.

     

    Best Regards

    Lucien

  • DataInsights's avatar
    DataInsights
    Icon for Super User rankSuper User
    5 years ago

    Anonymous,

     

    Hello again. I figured out a way to allow product filtering. The trick is using a disconnected product table, and adjusting the DAX in measure [ACV+YTD Est.].

     

    1. Create a disconnected table (no relationships) that contains a distinct list of all products in the fact table. You can do this in Power Query or DAX. DAX calculated table:

     

    Product Slicer = VALUES ( 'PBI Sample Data2'[Product] )

     

    2. Measure:

     

    ACV+YTD Est. = 
VAR vSelectedProduct =
    TREATAS (
        ALLSELECTED ( 'Product Slicer'[Product] ),
        'PBI Sample Data2'[Product]
    )
VAR vResult =
    CALCULATE (
        SUM ( 'PBI Sample Data2'[Product Value] ) + [Estimated addl ACV],
        vSelectedProduct
    )
RETURN
    vResult

    The TREATAS function is necessary to change the data lineage such that the selected product values from the Product Slicer table will be treated as if they are from the fact table, thus filtering the fact table.

     

    3. Create product slicer using the calculated table Product Slicer.

     

    Original post:

    https://community.powerbi.com/t5/Desktop/Forecast-with-DAX/m-p/1588854#M645202 

  • v-luwang-msft's avatar
    v-luwang-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi Anonymous ，

    Has your problem been solved, if so, please consider Accept a correct reply as the solution or share your own solution to help others find it.

     

     

    Best Regards
    Lucien