Looking for a best way to convert complex SQL filter in Power BI

[Anonymous]

I'm looking for a best way to implement a complex SQL filter in a Power BI report.

The filter is presently implemented in a database view fact table (SQL database), that is used as a data source for a corresponding model table. The filter condition is to be removed from this database view and is to implemented in Power BI report.

Here is the filter:-

WHERE X_FLAG = 'N'
AND DIM1.ATTR1 IN (VAL1, VAL2, VAL3 ...ETC)
AND DIM2.ATTR2 IN (VAL11, VAL22, VAL33 ..ETC)
AND DIM3.ATTR3 >= 101010 AND DIM3.ATTR3 <= 919191
AND (CASE WHEN DIM1 IN (VAL1) AND DATE_DIM >= SOME_DATE_VAL THEN
CASE WHEN DIM2.ATTR3 = 874 AND FCT_LOC_CODE='123'
...FEW MORE CONDITION THEN 0 ELSE 1 END 
ELSE 1 END ) = 1

So, this doesn't looks like easy.

The report connects to a SSAS model (the tabular model has all these dims and fact related).

One way I can think of is to implemented this logic somewhere in the tabular model as a flag and use this flag in the Power BI report. Now when the users need to have the data filtered by this logic they can use this flag in filter section, if not they can remove this attribute.

Are there any other ways to implement such a thing which would be more flexible for the end users?

[v-angzheng-msft]

Hi, @Anonymous 
Create the above condition with multiple measures. Then filter the items in the filter pane for measures equal to 1.
For example: 

M1 =
IF(
    [X_FLAG]
        IN SUMMARIZE( FILTER( 'table', 'table'[X_FLAG] = "N" ), [X_FLAG] ),
    1,
    0
)

M2 =
IF(
    [DIM1.ATTR1]
        IN SUMMARIZE(
            FILTER( 'table', 'table'[DIM2.ATTR2] IN { VAL11, VAL22, VAL33, ETC } ),
            [DIM1.ATTR1]
        ),
    1,
    0
)

If creating multiple measures is not what you want, you can still create just one measure to cover all of the above conditions.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.