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.
Hi there,
up front: I am a beginner in Power BI, DAX and Power Query.
I have the following problem:
Table "auction_results" contains data regarding projects that were awarded a tariff. Some the awarded projects also already have a permit which is a prerequisite to start operations. If this is the case the column "permit_id" contains a unique id. If not, the cell is empty.
Table "permit_data" contains detailed information on every project that obtained a permit for operations. Only some of these have already been awarded a tariff in the auctions. Every entry in this table has a "Anlagennummer" which is the connecting link to the "permit_id".
I have estbalished a n:1 relationship permit_id:Anlagennummer (was the only valid cardinality). My goal is to insert a conditional column named "tariff_status" in the table "Permit Data" that indicates whether the permitted project was already awarded a tariff or not. I tried the whole day at first using power query and afterwards DAX but I failed. In Power Query I did not find a function that equals the RELATED Function in DAX. In DAX I could only use RELATED in "permit_id", I guess because this is the n tables...?
As solution I would have expected sth like:
tariff = IF('permit_data'[Anlagennummer] = RELATED('auction_results'[permit_id]);"Yes";"No")
Out of desperation I also tried it the other way around:
permit_status = IF('auction_results'[permit_id] = RELATED('permit_data'[Anlagennummer]);"permitted"; "not permitted")
Whereas the first code showed nothing in table "permit_data", the second showed "permitted" for every project in table "auction_results" regardless whether the project was permitted or not...
Happy for help. It's probably very easy for you.
No one?
Do you need more information?
HI @hannes86,
I'd like to suggest drag Tariff measure to visual level filter of map visual and filter true value to achieve your requirement.
Regards,
Xiaoxin Sheng
Hi @v-shex-msft,
thank you for your reply.
My quesation rather was: How do I calculate the values yes/no in column "tariff" based on the information of Table1: 'auction_results' and Table2: 'permit_data'. If the value in column "Anlagennummer" in Table2 equals the value in column "permit_id" in Table1 then "Yes" if not "No".
As solution I would have expected sth like below, but it did not work.
tariff = IF('permit_data'[Anlagennummer] = RELATED('auction_results'[permit_id]);"Yes";"No")
Current relationships:
auction results permit_data
permit_id n:1 Anlagennummer
Thank you.
Hannes
Hi @hannes86,
Power bi not support create dynamic calculate column/table based on filter/slicer, so your requirement can't be achieved.
You can only use measure to achieve dynamic calculation.
Calculated Column/Table Change Dynamically According to Slicer Selection in the Report.
Regards,
Xiaoxin Sheng
Sample data please.
Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
INPUT
Table1: 'auction_results'
bidder | bid_id | award_id | transaction_no | quantity | WTG_n | bid_id_WTG | permit_id |
bidder1 | WIN18-1-001 | 115420405023 | 1 | 1 | WIN18-1-001-01 | A4497640206941 | |
bidder2 | WIN18-1-002 | 115420999999 | 1 | 1 | WIN18-1-002-01 |
Table2: 'permit_data'
date | notification | Anlagennummer | OEM | Type | Height | Diameter |
12.10.2015 | permit | A1139377067008 | Siemens Wind Power GmbH & Co. KG | SWT-6.0-154 | 110 | 154 |
21.12.2017 | permit | A4497640206941 | ENERCON GmbH | E-115 E2 | 149,1 | 115,7 |
Current relationships:
auction results permit_data
permit_id n:1 Anlagennummer
Desired OUTPUT:
date | notification | Anlagennummer | OEM | Type | Height | Diameter | Tariff |
12.10.2015 | permit | A1139377067008 | Siemens Wind Power GmbH & Co. KG | SWT-6.0-154 | 110 | 154 | no |
21.12.2017 | permit | A4497640206941 | ENERCON GmbH | E-115 E2 | 149,1 | 115,7 | yes |
Desired Visualization afterwards
Example: Show only projects/volumes with a permit but without a tariff
I hope this helps
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 |
---|---|
98 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |