Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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
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
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!!
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
Thanks for your response!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |