Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Filter rows to adjacent column month

The following measure works fine when my matrix has 'Calendar'[Date Hierarchy] in the columns and 'sales'[Order Date] in the rows.  However, when I try to rever to the desired layout of 'Calendar'[Date Hierarchy] in the columns, and "Division" in the rows, the measure returns no values.

 

How can I return the same values from the first screenshot, but not have it be dependent on [Order Date] remaining in the matrix?

 

 

CountCompleteEstimate = 
CALCULATE(
    DISTINCTCOUNT(SalesHistory[SO]),
    FILTER(SalesHistory,SalesHistory[EstimateQuality]="Complete Estimate"),
    FILTER('Calendar','Calendar'[MonthInt]=SELECTEDVALUE(Sales[OrderDateMonthInt])
    &&'Calendar'[Year]=SELECTEDVALUE(Sales[OrderDateYear])
    )
)

 

 

Thanks in advance!

Need Help

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous ,

    Please update the formula of measure [CountCompleteEstimate] as below and check whether the matrix can display the data or not:

    CountCompleteEstimate =
CALCULATE (
    DISTINCTCOUNT ( 'SalesHistory'[SO] ),
    FILTER ( 'SalesHistory', 'SalesHistory'[EstimateQuality] = "Complete Estimate" )
)

    If the above one is not working, please share some sample data in SalesHistory, Sales & Calendar table and your expected result with backend logic and special examples (exclude sensitive data) in order to provide you a suitable solution? Please also share the related relationship info(cardinality, direction etc.) if there is any relationship exist in these tables. Thank you.

    Best Regards