Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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

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.   ...
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    5 years ago

    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's avatar
Anonymous
Not applicable
5 years ago

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's avatar
Anonymous
Not applicable
5 years ago

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's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        Anonymous
        Not applicable
        5 years ago

        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