Variable table not containing rows from another table

[AndreiiBC]

Hello,

I have a following FACT table with the following structure:

Category	SubCategory	Product	Price	Period
CatA	SCA	SCA-Product1	2	Q1-2024
CatA	SCA	SCA-Product2	3	Q2-2024
CatB	SCB	SCB-Product1 - Discounted	1	Q4-2023
CatB	SCB	SCB-Product2	4	Q2-2023
CatC	SCC	SCC-Product1	5	Q1-2024
CatC	SCC	SCC-Product2	2	Q3-2023
CatC	SCC	SCC-Product3	3	Q3-2023
CatD	SCD	SCD-Product 1 - Discounted	1	Q2-2023
CatD	SCD	SCD-Product2	3	Q1-2023

Now, let's take the following scenario. The user will select from the right filter panel a Product. Now 2 things can happen:

- Product is discounted - We need to take the Discounted price and only that product from the subcategory. For example, if the user filters on SCB-Product1 - Discounted, we will use only that line from the SCB subcategory, SCB Product2 will be removed from the calculation. Same for SCD, We will take only PRoduct1 - Discounted and exclude Product2. Same thing will happen if a user select from the same subcategory, one or more than a discounted product + any other additional products. Let's say we have Subcategory E, user select Product1, Product2, Product3, Product4 - Discounted, Product5 - Discounted. We will sum only Product 4 and 5 and exclude 1, 2, 3 from the calculation.

- User selects any product which is not discounted, we sum them. Or selects a discounted product from Subcategory B and a regular product from Subcategory C, we still add them because they belong to a different subcategory.

How I was thinking of doing this is to first create a variable table which contains only the rows containing the discounted products. So we will get a table containing:

Category	SubCategory	Product	Price	Period
CatB	SCB	SCB-Product1 - Discounted	1	Q4-2023
CatD	SCD	SCD-Product 1 - Discounted	1	Q2-2023

Then, I want to create a second table which contains the other rows, but excluding the SubCategories which are already in the above table. Why ? Because for example if I don't remove all the rows for SCB, in the next table I will still get SCB-Product2 which shouldn't be sumed because SBC has a discounted product. So the second table should contain only the subcategories which don't have a discounted product, like this:

Category	SubCategory	Product	Price	Period
CatA	SCA	SCA-Product1	2	Q1-2024
CatA	SCA	SCA-Product2	3	Q2-2024
CatC	SCC	SCC-Product1	5	Q1-2024
CatC	SCC	SCC-Product2	2	Q3-2023
CatC	SCC	SCC-Product3	3	Q3-2023

In the end, after any user selection, I was thinking to do a SUM from both tables, then sum the two sums and it should get the corect result. If the user selects a regular product the first table will be empty and it will sum from the second table. If it selects both a discounted and a regular from the same category, it will sum from the first table and the second will be empty etc.

Any idea how can I do this ? I was thinking to use EXCEPT but I am not sure if excepts removes just the common rows, so from example it will only exclude the rows with discounted but keep the other rows from the same category, I need something like a NOT IN in SQL in which the argument will be subcategory NOT IN the list of distinct subcategories from the first table.

Hope it makes sense, if you need any other clarification please let me know.

Thank you !

[lbendlin]

 The user will select from the right filter panel a Product. 

That needs to come from the product dimension table.

[AndreiiBC]

That is true. Small update on my progress:

1) First I tried to extract from the FACT, all the subcategories that have a discounted product

VAR listofsubcat = SELECTCOLUMNS( FILTER( FACT, FACT[FLAG_Product_Discount] = 1), -- just a flag I did in the backend to identify easily the discounted products "DiscountedSubcat", RELATED(DIM[Subcategory Code]) )

2) Then I tried to calculate the SUM of prices for the remaining products, but whatever I am trying, I get a correct total sum, but in incorrect row level sum, by period:

I tried many variations to get the result similar to the below logic:

VAR listofsubcat2 = SELECTCOLUMNS(FILTER(DIM, DIM[Subcategory Code] IN listofsubcat), "_subcat", DIM[Subcategory Code])

RETURN SUMX( ADDCOLUMNS(FILTER(FACT, NOT (RELATED(DIM[Subcategory Code) IN listofsubcat2) ), "_price", FACT[Price] ), [_price] )

Here listofsubcat2 correctly extracts the subcategories which have a discounted product, if I hardcode the name in the measure in the NOT IN part, like that :

RETURN SUMX( ADDCOLUMNS(FILTER(FACT, NOT (RELATED(DIM[Subcategory Code) IN {"Subcat1", "Subcat2" etc) ), "_price", FACT[Price] ), [_price] )

--> it works, but of course I need the formula to be dynamic based on user's selection.

Tried also with calculatetable:

RETURN SUMX(CALCULATETABLE(FACT, NOT ( DIM[Subcategory Code] IN listofsubcat2 ) ), FACT[Price])

Same wrong result. Apparently Power BI is somehow overwriting my filter context while doing the individual row sum ? Also I checked further and even if some periods show the correct sum, if you do a countrows in Power BI vs the returned rows in SQL, the number is different so probably "behind the curtains" they are wrong as well.

Any idea what am I doing wrong and how can I make Power BI respect my filter without having to manually hardcore the values that I want to exclude ?

Thank you !

[lbendlin]

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[AndreiiBC]

Hi @lbendlin ,

You can find the pbix file here : https://we.tl/t-QviFhwzCih

You also have an example there with the result I get now vs. the expected result.

Thank you!