0

Filtering discoveries with OR instead of AND

I have been asked for a report to show invoices where the delivery OR the invoice has been sent to a particular country. I have an IMDB model using a sql source, that has the two countries as [Dlv Country] and [Inv Country]

For example if I have both of these in the report slicers and select my chosen country in both- let's say I want GBR, this will show me all records where [Dlv Country] = GBR AND [Inv Country] = GBR. But if we have an invoice that has been delivered to GBR but invoiced to DEU, this will not show because [Inv Country] is not GBR

Is there a way I can get a report to filter records where [Dlv Country] = GBR OR [Inv Country] = GBR?

Thanks

1 reply

null
    • imbarmarinescubar_pyram
    • 2 yrs ago
    • Reported - view

    Hi Sophie durrant ,

    There isn't a direct option to change the slicers into OR.

    Something you can try out is to create a custom list of invoices that is a union of the 2 lists:
    Invoices where Dlv=GBR and invoices where Inv=GBR.

    It should looks something like this:

    {nonempty([table].[invoices].allmembers,[table].[Dlv Country].[GBR]),
nonempty([table].[invoices].allmembers,[table].[Inv Country].[GBR])}

    You can also use parameters instead of the actual GBR items.

    Now use the custom list inside your discovery, without the 2 filters you had before.
    If you use parameters instead of static values, then you will get the parameters as slicers in the discovery, filtering out the list of invoices.

    Imbar

Login to reply

Content aside

  • Status Answered
  • 2 yrs agoLast active
  • 1Replies
  • 46Views
  • 2 Following