Forum Discussion

sppt's avatar
sppt
Frequent Visitor
2 years ago

Filter values that 'are not present'.

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 fi...
Need Help
Daniel29195's avatar
Daniel29195
Community Champion
2 years ago

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's avatar
    sppt
    Frequent Visitor
    2 years ago

    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