Power BI "OR"

[grkanth81]

Hi guys,

I have the below test data and I have a requirement where i would like to have one slicer named Period with the drop down values from both WorkedPeriod and BilledPeriod. And for example when the user select the period 0319, then it should display the records that are highlighted in yellow. (so it has to do the OR). (and please note i may not be able to do the union of WorkedPeriod records and BilledPeriod records because there are millions of rows)

Is this possible in Power BI?

I tried putting 2 slicers for Worked Period and Billled Period and when i selected the period 0319 in both slicers it was doing the AND and returning only 2 records.

Any alternatives or suggestions to achieve this would be appreciated.

Thanks,

rav

[Anonymous]

Hi @grkanth81 

Have you tried spliting the table into two ( Matter and work period) and (Matter and Billed period) and use Append in Query Editor.  ( I assume you have only these 3 column )

All the best!

[grkanth81]

Hi Lw,

Thanks for the reply.

Yes there are some other columns also there involved in my dataset. And also please note that there would be around 9 million records.

So are there any other options other than using the Append?

Thanks,

rav

[d_gosbell]

You could create a measure like the following, note that this measure needs to be visible somewhere in your visual for it to work. You mentioned that you wanted a single "Period" slicer, so for this to work you'd be best to have a single 'Period' table in your model (which may or may not be related to the WorkedPeriod and BilledPeriod columns) - the expression below uses the CROSSFILTER() function to turn off any potetially active relationships.

IsInSelection = 
CALCULATE(COUNTROWS(   
    FILTER(
        'Table',
        IF(ISFILTERED('Period'[Period]), 'Table'[WorkedPeriod] IN VALUES(Period[Period]))
        || IF(ISFILTERED('Period'[Period]),'Table'[BilledPeriod] IN VALUES(Period[Period]))
    )
)
,CROSSFILTER('Table'[WorkedPeriod], Period[Period],None)
,CROSSFILTER('Table'[BilledPeriod], Period[Period], None)
)

[grkanth81]

Hi gosbell,

I have created a new tabled named Period and linked it to the Billed Period and Worked period columns from my main table and tried to create a measure with the syntax which you have provided.

But i am getting empty/blank result set.

Thanks,

Rav

[Anonymous · Accepted solution]

Hi, @grkanth81 

1. Create a Table by using this formula.

SLicer = DISTINCT(Union(Values(Sheet1[BilledPeriod]),VALUES(Sheet1[WorkedPeriod])))
- you can rename the column to Period

2. Than create a Measure in your exisiting table by using this :

Measure = if(min('Sheet1'[BilledPeriod]) IN VALUES(SLicer[Period]) || Min('Sheet1'[WorkedPeriod]) IN VALUES(SLicer[Period]),1,Blank())

Now in a table visualization add all the fields including measure. Add the slicer from the table you created.

But before all these steps:

Make sure you add the  Index Col so that you get all the leading zeros.

(Screenshot for your reference)

1. With selection (0319)

Hope this helps. Let me know for any question.

Thanks,

Tejaswi

[grkanth81]

Thanks Tejaswi.

[d_gosbell]

If you did not already have a Period table you can just avoid creating any relationships between the Period table and your other table and use a manual filter statement to do the OR join. My previous expression was part of a more complicated example and actually had some extra complexity in it as it was forcing nothing to be displayed if no filters were selected.

But I would suggest using something like the following using VALUES() as it will work if multiple periods are selected where as the approach using MIN() will only filter on the minimum of the selected periods if more than one is filtered.

IsInSelection =
VAR _selectedPeriods =
    VALUES ( 'Period'[Period] )
RETURN
    COUNTROWS (
        FILTER (
            'Table',
            'Table'[WorkedPeriod] IN _selectedPeriods
                || 'Table'[BilledPeriod] IN _selectedPeriods
        )
    )