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 ,

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, 

     

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

      Anonymous 

    • 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