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 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])

 

Need Help

2 Replies

  • 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