Filter values that 'are not present'.

[sppt]

I hope I can describe the topic reasonably well. We have a cash register system that, in the case of discounts, does not flag the receipt itself, but only the receipt lines.

So if we now filter by a discount campaign in Power BI Desktop, we will of course only see those lines that have this marking. 

It also filters the receipt numbers that include the "Father's Day campaign" flag. So far, so good.

However, if I don't filter by the discount reason but by an individual receipt, we actually get exactly the result we need - but of course only on a receipt level and not across all receipts.

Of course, if we don't filter anything at all, the result is also "correct" - but we can't determine the turnover generated "despite" or "thanks to" a single discount campaign.

How is it possible to represent something similar in Power BI Desktop as in SQL?

For example, my thought would be something like:

SELECT [Receipt], [Turnover] FROM [Table1] 
WHERE [Table1].[Receipt] IN (SELECT [Receipt] FROM [TableWithReceiptNoAndDiscountReasonColumn])

[Daniel29195]

@sppt 

SELECT [Receipt], [Turnover] FROM [Table1] 
WHERE [Table1].[Receipt] IN (SELECT [Receipt] FROM [TableWithReceiptNoAndDiscountReasonColumn])

measure as follow : 

var ds_1 =  values(table1[receipt]) 

var ds_2 = values(TableWithReceiptNoAndDiscountReasonColumn[receipt]

var res = 

intresect ( ds_1 , ds_2 ) 

return 

switch( true()  ,  tbl[rec] in  res , 1 , blank() ) 

you can use this measure in the filter pane on the visual level.  --> is  =  1  .  or not is blank() . 

or you can instead of 

return 

switch( true()  ,  tbl[rec] in  res , 1 , blank() ) 

use : 

calculate (  count( tbl[receipt) , keepfilters( res ) ) 

let me know if this helps .

[sppt]

Hi Daniel,

I probably didn't express myself clearly. My data in Power BI looks as follows:

If I didn't have a tabular model, I would now create such an additional table via SQL and create a filter that gives me all receipts with the discount reason "20-10". It doesn't matter if this column is filled or not. It is sufficient that a single receipt line includes this discount to claim that we obtained the remaining turnover only because of this discount reason.

br

Patrick