Tableau Filter Logic in Power BI DAX

[kapildua16]

Hi Power BI Community,

I'm working on replicating a Tableau calculation and filter logic in Power BI using DAX, and I could use your expertise. Here's the scenario:

Current Implementation in Tableau

1. Filter Name: Outlier Check

if [Calc_Premium] >= [Calc_Premium_Lowerbound]
and [Calc_Premium] <= [Calc_Premium_Upperbound]
then 'Valid'
else 'Outlier' END

Supporting Calculations:

Calc_Premium:
{Fixed [Country],[Clientid],[PlanDesignYear],[Producttype]:Min([Premium Min])}

Calc_Premium_Lowerbound

IF [Calc_Premium_Q1] - [Calc_Premium_IQR] * 1.5 < 0 THEN 0
ELSE [Calc_Premium_Q1] - [Calc_Premium_IQR] * 1.5 END

Calc_Premium_Upperbound:

IF [Calc_Premium_Q3] + [Calc_Premium_IQR] * 1.5 < 0 THEN 0
ELSE [Calc_Premium_Q3] + [Calc_Premium_IQR] * 1.5 END

Calc_Premium_IQR:
[Calc_Premium_Q3] - [Calc_Premium_Q1]

Progress in Power BI

• I have already calculated the measures for Calc_Premium_Q1 and Calc_Premium_Q3 using DAX:

VAR MinRateByClient =
SUMMARIZE(
'Plan',
'Plan'[Country],
'Plan'[ClientID],
'Plan'[Producttype],
'Plan'[PlanDesignYear],
"MinimumRate", MIN('Plan'[Premium_Min])
)
VAR _Selected =
SELECTCOLUMNS(
MinRateByClient,
"Country", [Country],
"Producttype", [Producttype],
"PlanDesignYear", [PlanDesignYear],
"MinRate", [MinimumRate]
)
VAR Cal_Q1 =
ADDCOLUMNS(
SUMMARIZE(
_Selected,
[Country],
[Producttype],
[PlanDesignYear]
),
"Median", PERCENTILEX.INC(
FILTER(
_Selected,
[Country] = EARLIER([Country]) &&
[Producttype] = EARLIER([Producttype]) &&
[PlanDesignYear] = EARLIER([PlanDesignYear]) &&
[MinRate] <> BLANK()
),
[MinRate],
0.25
)
)
RETURN

AVERAGEX(Cal_Q1, [Median])

How can I replicate the Tableau filter Calc_Premium_Outlier_Check in Power BI? Specifically:

• Translate the Tableau logic to DAX for checking if Calc_Premium falls within Calc_Premium_Lowerbound and Calc_Premium_Upperbound.

• Implement this as a measure or calculated column in Power BI.

I appreciate any guidance or example implementations. Thanks in advance for your help!

[v-tsaipranay]

Hi @kapildua16 

Thanks for reaching out to the Microsoft Fabric Community Forum!

To help you with implementing DAX measures for detecting outliers in Power BI, I’ve created a sample dataset and set up the necessary DAX measures. I noticed from your query that you’ve already calculated the measures for Calc_Premium_Q1 and Calc_Premium_Q3—that's great!

To move forward and achieve your goal, there are a few more measures you’ll need to create. Here are the DAX measures you can implement:

Calc_Premium:

Calc_Premium =

CALCULATE(  

    MIN('Sheet1'[Premium_Min]),  

    ALLEXCEPT('Sheet1', 'Sheet1'[Country], 'Sheet1'[ClientID], 'Sheet1'[Producttype], 'Sheet1'[PlanDesignYear])  

)

Calc_Premium_IQR:

Calc_Premium_IQR =

[Calc_Premium_Q3] - [Calc_Premium_Q1]

Calc_Premium_Lowerbound:

Calc_Premium_Lowerbound =  

VAR LowerBound = [Calc_Premium_Q1] - ([Calc_Premium_IQR] * 1.5) 

RETURN IF(LowerBound < 0, 0, LowerBound)

Calc_Premium_Upperbound:

Calc_Premium_Upperbound =  

VAR UpperBound = [Calc_Premium_Q3] + ([Calc_Premium_IQR] * 1.5) 

RETURN IF(UpperBound < 0, 0, UpperBound)

Calc_Premium_Outlier_Check:

Calc_Premium_Outlier_Check =  

VAR CurrentPremium = [Calc_Premium] 

VAR LowerBound = [Calc_Premium_Lowerbound] 

VAR UpperBound = [Calc_Premium_Upperbound] 

RETURN  

    IF(CurrentPremium >= LowerBound && CurrentPremium <= UpperBound, "Valid", "Outlier")

Now, go to report view and drag and drop matrix visual onto canvas and add following fields:

Country, ClientID, Producttype,  PlanDesignYear,   Premium_Min, Calc_Premium, Calc_Premium_Outlier_Check

I hope this information will help you replicate the Tableau filter "Calc_Premium_Outlier_Check" in Power BI. If you have any more questions or need further assistance, please feel free to reach out.

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

Thank you

[v-tsaipranay]

Hi @kapildua16 ,
As mentioned in the steps provided in my pervious reply, please proceed to follow them. Additionally, I have attached the PBIX file that I attempted to implement.

Thankyou

[kapildua16]

HI @v-tsaipranay ,
Thanks so much for taking the time out and providing the logic, but the result is not matching with Tableau result due to both tools works differently with aggregation. As Tableau is working on row level context and power bi measure working on filter context, so i am not getting correct answer in output and i want to create outlier check as filter

[v-tsaipranay]

Hi @kapildua16 

Thank you for reaching out to the Fabric Community Forum.

I understand that you're encountering differences in results between Tableau and Power BI due to the way each tool handles aggregation. Since this forum focuses on Power BI and Fabric, I'm unable to assist with Tableau-specific questions. However, the difference in aggregation methods Tableau’s row-level context versus Power BI’s filter context can definitely cause these discrepancies.

Please refer below link on how to raise a contact support or support ticket.
https://learn.microsoft.com/en-us/power-bi/support/create-support-ticket

If you have any questions related to Power BI or Fabric, I’d be happy to help. Please feel free to reach out.
Thank you.

[Bibiano_Geraldo]

Hi @kapildua16 ,

This Calc_Premium_IQR measure:

Calc_Premium_IQR = 
[Calc_Premium_Q3] - [Calc_Premium_Q1]

Calc_Premium_Lowerbound measure

Calc_Premium_Lowerbound = 
IF(
    [Calc_Premium_Q1] - [Calc_Premium_IQR] * 1.5 < 0,
    0,
    [Calc_Premium_Q1] - [Calc_Premium_IQR] * 1.5
)

Calc_Premium_Upperbound measure

Calc_Premium_Upperbound = 
IF(
    [Calc_Premium_Q3] + [Calc_Premium_IQR] * 1.5 < 0,
    0,
    [Calc_Premium_Q3] + [Calc_Premium_IQR] * 1.5
)

Calc_Premium measure

Calc_Premium = 
VAR MinRateByClient =
    ADDCOLUMNS(
        SUMMARIZE(
            'Plan',
            'Plan'[Country],
            'Plan'[ClientID],
            'Plan'[Producttype],
            'Plan'[PlanDesignYear]
        ),
        "MinRate", MIN('Plan'[Premium_Min])
    )
RETURN
    AVERAGEX(MinRateByClient, [MinRate])

Outlier Check measure

Outlier_Check_Measure = 
VAR CalcPremium = [Calc_Premium]
VAR LowerBound = [Calc_Premium_Lowerbound]
VAR UpperBound = [Calc_Premium_Upperbound]
RETURN
IF(
    CalcPremium >= LowerBound && CalcPremium <= UpperBound,
    "Valid",
    "Outlier"
)

[Bibiano_Geraldo]

Hi @kapildua16 , did you tried this my solution?