IF Statement - Two tables - No relationship - Help needed!

[Anonymous]

Hi fellow Power BI experts and knowledgeable users!

Hope everyone is well.

This is my first post, feel free to critise and give feedback on the way I should present and ask questions etc.

I still consider myself an ammateur with Power BI since only have a few months of self taught experience under my belt, thus apologies for any wrong terminology and lack of technical knowledge using DAX.

Objective: Create a conditional column that classifies a column with two outcomes 'Within limit' and 'Exceeded'. 

- Table 1 encloses the numerical figure that need's to be checked if that number has 'Exceeded' the limit or is 'Within Limit'.

- Table 2 defines the package type and limit, for example package type 'Bronze' has a data consumption limit of '2500' and 'Silver' has a limit of '20,000' etc

IF table1.column9 > table2.column7 then "Exceeded" else "Within Limit"

Context: I am checking If data usage has been exceeded.

table 1 and table 2 arent directly connected but have connections through other tables. I will include a snapshot of lineage below.

Problem: How do you create a conditional column on two tables that have no direct relationship? (There are tables between them that are joined).

 

Lineage:

 

Current  output:

Desired output: 

[Anonymous · Accepted solution]

Hi @Anonymous ,

Please update the formula of your calculated column as below:

new column =
VAR _count =
    COUNTX (
        FILTER (
            'membership_package',
            1 = 1   //please add the proper condition here
                && 'appended_table'[lwin11_count] > 'membership_package'[annual_data_allowance]
        ),
        'appended_table'[lwin11_count]
    )
RETURN
    IF ( _count > 0, "Exceeded", "Within Limit" )

If the above one is not working, please provide the relationship fileds which these four tables created the relationship base on and some sample data (exclude sensitive data) from them. And 'appended_table'[lwin11_count] and'membership_package'[annual_data_allowance] is a one-to-one relationship, that is, one 'appended_table'[lwin11_count] corresponds to only one'membership_package'[annual_data_allowance] value? It is better if you can provide your sample pbix file(exclude sensitive table) if it is convenient. Thank you.

Best Regards

[Anonymous]

Hi, 

Sorry for the late response. Been manic, appreciate your support and commentary!

Unfortunately, I am not too clear about what condition I am meant to enter in the code you have provided, please advise.

Thank you,

Syed

[Anonymous]

Hi @Anonymous ,

Actually, it depends on your requirement. If there is no other condition need to be fulfilled besides that the condition  'appended_table'[lwin11_count] > 'membership_package'[annual_data_allowance], you can just keep the formula of the related calculated column as below:

new column =
VAR _count =
    COUNTX (
        FILTER (
            'membership_package',
           'appended_table'[lwin11_count] > 'membership_package'[annual_data_allowance]
        ),
        'appended_table'[lwin11_count]
    )
RETURN
    IF ( _count > 0, "Exceeded", "Within Limit" )

If the above one still can't get your desired output, please provide your sample pbix file(exclude sensitive data). I will check the file and provide a suitable solution for you. Thank you.

Best Regards

[Anonymous]

Hi,

Thank you for your prompt response! Really appreciated.

@Anonymous No luck on my side. I have attached a link to download the file (expires in 7 days).

https://1drv.ms/u/s!AqIxxqW7thm1jUepZ62z7BOtkxlD

Kind regards,

Syed

[Anonymous]

Hi @Anonymous ，

As checked your sample pbix file, what you are creating is a measure not a calculated column. Please create a calculated column with the below formula just as shown in below screenshot:

UNDERSTANDING THE DIFFERENCES BETWEEN CALCULATED COLUMNS & MEASURES IN POWER BI

Calculated Columns and Measures in DAX

Best Regards

[Anonymous]

Hi @Anonymous,

Good spot! thank you. It appears to be only showing exceeded for them all and not within limit?

kind regards,

Syed Mahmood

[Anonymous]

Hi @Anonymous ，

Please create a calculated column as below to get it, you can find the details in the attachment.

Column = 
VAR _midmp = 'appended_table'[m_id]
VAR _mercid =
    CALCULATE (
        MAX ( 'merchant_group'[merchant_id] ),
        FILTER ( 'merchant_group', 'merchant_group'[m_id] = _midmp )
    )
VAR _prefvalue =
    CALCULATE (
        MAX ( 'merchant_broker_pref'[pref_value] ),
        FILTER ( 'merchant_broker_pref', 'merchant_broker_pref'[mg_id] = _mercid )
    )
VAR _ada =
    CALCULATE (
        MAX ( 'membership_package'[annual_data_allowance] ),
        FILTER (
            'membership_package',
            FORMAT ( 'membership_package'[id], "#" ) = _prefvalue
        )
    )
RETURN
    IF (
        _ada = BLANK (),
        BLANK (),
        IF ( 'appended_table'[lwin11_count] > _ada, "Exceeded", "Within Limit" )
    )

Best Regards

[Anonymous]

Hi @Anonymous ,

On further review, it seems to be miss classifying some. if you compare lwin_11 to the annual limit some arent correctly classified. Please could you re-look? or maybe its something from my side.

[Anonymous]

@Anonymous 

[Anonymous]

Thank you so much @Anonymous, you the best!

[Anonymous]

@Anonymous 

[Anonymous]

pinging fellow power bi users for urgent help! The report due date is over due now.

@amitchandak @selimovd @Fowmy @parry2k @v-robertq-msft @Anonymous @Anonymous 

[amitchandak]

@Anonymous , You can create a column in table 1 like.

But I till think more information is needed to build this column

example

new column =
var _cnt = count(filter(Table2, table1[column9] > table2[column7]),table2[column7])
return
if(not(isblank(_cnt)), "Exceeded" , "Within Limit")

refer 4 ways to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8

[Anonymous]

Hi @amitchandak,

Thank you for your prompt response.

I checked out the links sent, very helpful gaining knowledge on the techniques to add a column but unfortunately didnt understand a way to do it in my sceniro since the table connection isnt intuitive to me.

What further information is needed? 

Any further help would be much appreciative.

Kind regards,

Syed