Calulate sales filtered differently in the same measure.

[Mister_D1979]

Hello!

I have an issue with a measure that needs to look at customer sales for a chosen period on a product level, and then using that as a filter on another calculation which looks at sales on the Product Category level for the preceding 12 months. I have DAX code that looks like this :

VAR _ProductPresence = FILTER( ALLSELECTED( Customer[dmCustomerID] ), [Quantity KFP] > 0 )

RETURN

     CALCULATE(
          SUM( Invoice[QuantityKFP] )
          , REMOVEFILTERS( Product[SKU] )
          , _ProductPresence
          , DATESBETWEEN( 'Time'[Day]
               , FIRSTDATE( DATESINPERIOD( 'Time'[Day], LASTDATE( 'Time'[Day] ), - 12, MONTH ) )
               , LASTDATE( 'Time'[Day] )
          )
     ) + 0

The tables involved are Invoice and product, which are in a one-to-many relationship through a productID. Filters used are Date, Product Category ,Product SKU and Customer. 

For example, a user chooses :

     The time period 202401 

     The customers A, B and C

     The Product Category 'Furniture'

     The Product SKU 'Table 34' which happens to belong to the above Product Category. (Only one SKU can be chosen at a time).

Customers A and C have had sales during 202401 and Customer B has not. The expected result should the be 0 for Customer B. For Customers A and the the returned result should show their sales of the chosen Product Category for the preceding 12 months. This works, as long as the chosen SKU belongs to the chosen Product Category. 

Should the user instead pick the SKU 'Red Paint', which does not belong to Product Category 'Furniture', the result is a blank row. I do understand this (I think), since Product Category and Product SKU are in the same table, so choosing a SKU from a different Product Category would effectively land you on a different ProductID. I do not have extensive experience with DAX, so my question is; Is there anyway to circumvent this using DAX, or does the underlying dataset need to be remodeled?

Thanks in advance!

/David

[Mister_D1979]

Indeed I could, but the user is supposed to choose a Product Category together with a SKU. That is the issue when a user chooses a SKU that is not belonging to the chosen PRoduct Category.

[lbendlin]

You don't have a technical problem. What you have is a user education problem.  Explain to them how filters work in Power BI.

[lbendlin · Accepted solution]

The filter on Product Category "Furniture"  will stay until the user clears it. Together with the "Red Paint"  SKU this will indeed result in a blank result.   You could use the DAX function REMOVEFILTERS  on the Product Category, but then you could also just not have that filter in the first place.