Forum Discussion

kapildua16's avatar
kapildua16
Icon for Helper I rankHelper I
1 year ago

Tableau Filter Logic in Power BI DAX

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!

 

Need Help

6 Replies

  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    Icon for Super User rankSuper User
    1 year ago

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

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      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's avatar
        kapildua16
        Icon for Helper I rankHelper I
        1 year ago

        HI Anonymous ,
        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