Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
cbtekrony
Resolver I
Resolver I

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
 
cbtekrony_0-1623938521304.png

 

3 REPLIES 3
v-luwang-msft
Community Support
Community Support

Hi @cbtekrony ,

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

DataInsights
Super User
Super User

@cbtekrony,

 

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 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




v-luwang-msft
Community Support
Community Support

Hi  @cbtekrony ,

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

 

Best Regards

Lucien

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.