IF Statement Help

[New2PowerBI]

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! 

[v-yulgu-msft · Accepted solution]

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

[New2PowerBI]

Thank 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!!

[DoubleJ]

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

[New2PowerBI]

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?

[DoubleJ]

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

[New2PowerBI]

Thanks for your response!