How to remove filters from the fact table keeping filters on the dimension table?

[gvg]

Hi folks,

I am looking for any hint for this unusual scenario. I have Products (dimension) and Sales (fact) tables that are related through 1-many relationship. If I use ALL(Sales) in my measure, it removes filters not only from Sales, but from the Products table too. How do I remove filters for the Sales table only?

[parry2k]

@gvg I'm still not able to understand the problem you are trying to solve, you just gave the structure of the tables and the output, but not sure what problem we are solving here.

[gvg]

My problem is that I have to work with unusual data tables were some dimensions are in the fact tables. This is a simplified example of my tables:

Products[ID]

Products[Title]

Sales[Date]

Sales[Color]

Sales[Amount]

I need to produce the report like this:

Products[Title]	Week No	Sales[Color]	Sales[Amount]	Sales[Color]=green
abc	12	Red	10000	5000
cde	12	Black	4000	5000

[Anonymous · Accepted solution]

Hi

@parry2k ， thanks for your concern about the problem and i want to offer some more information for user to refer to. 

hello @gvg , you can refer to the following solution.

Sample data

Sales

Product

Calendar table

Relationship

Create a measure

Sum_greencolor =
IF (
    SUM ( Sales[Amount] ) <> BLANK (),
    CALCULATE (
        SUM ( Sales[Amount] ),
        ALLEXCEPT ( Sales, Products[Title], 'Calendar'[Week] ),
        Sales[Color] = "green"
    )
)

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[parry2k]

@gvg what are you trying to achieve? What is your end goal?