Forum Discussion
IF Statement Help
I'm new to Power BI and would like some help.
I have a table with values:
FREQUENCY (with numerical values) and FREQUENCY UNITS (with alphanumeric values)
I have another table that provides a "key" of the value (Annual WO Impact column). For example, If Frequency is 1 and Frequency Unit is Months, the Annual WO Impact column notes "12". What I want to do is figure out cost based on the Annual WO Impact result (number multiplied by $30); not sure how to connect the two Tables together of if I even need to, I may be making it more difficult than what is needed.
New to Power BI and community; any help will be greatly appreciated! :smileyhappy:
Hi New2PowerBI,
In your scenario, please add a calculated column in the table(in my test, it's named 'Fact Table') which contains thousands of records using below DAX formula:
KEY = LOOKUPVALUE ( 'Key Table'[Annual WO Impact], 'Key Table'[FREQUENCY], 'Fact Table'[FREQUENCY], 'Key Table'[FREQUNIT], 'Fact Table'[FREQUNIT] )Thanks,
Yuliana Gu
6 Replies
If I got you right: In the "keys table" there is a key column consisting of 2 values (frequency and frequency unit) that match values in the "values" table?
That would mean you need a way to create keys columns in both tables and then combining these 2 values and then relate these two tables by these key columns. Is there a way to clearly distinguish the values? How many different frequency values and different frequency unit values can appear? It might help if you provided more examples.
JJ
Here is a visual of what I have:
FREQUENCY FREQUNIT Annual WO Impact 1 MONTHS 12 1 WEEKS 52 1 YEARS 1 2 MONTHS 6 2 WEEKS 26 2 YEARS 1 3 MONTHS 4 3 YEARS 1 4 YEARS 1 5 YEARS 1 That is one table; then I have another Table that has thousands of records and each record has a FREQUENCY and FREQUNIT, but not an Annual WO Impact. What I want is to add a column to the table that notes what the Annual WO Impact is according to this key, first step, and then once that is in place I think I can multiply the value by "x" number of $ and get a cost for that line item. Does that help?
What you could do:
1) In both tables create a key column concatenating the frequency and the unit, delemited by a special character like "_"
Key = [FREQUENCY] & "_" & [FREQUNIT]
2) relate the tables by these columns
3) because it is an many:1 relationship you can now access the [Annual WO Impact] column as it were in the same table and do your calculations
Hope this helps
JJ
Hi New2PowerBI,
In your scenario, please add a calculated column in the table(in my test, it's named 'Fact Table') which contains thousands of records using below DAX formula:
KEY = LOOKUPVALUE ( 'Key Table'[Annual WO Impact], 'Key Table'[FREQUENCY], 'Fact Table'[FREQUENCY], 'Key Table'[FREQUNIT], 'Fact Table'[FREQUNIT] )Thanks,
Yuliana GuThank You!!! Worked like a charm! I have a lot of similar scenarios; I'll be able to apply this to more than just one problem. Thanks again!!
