Forum Discussion
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.
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:
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
14 Replies
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=czNHt7UXIe8Hi 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
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
