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 product...
Need Help
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