logical comparison (true/false) on different columns in different tables

[hannes86]

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.

[hannes86]

No one?

Do you need more information?

[v-shex-msft]

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

[hannes86]

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

[v-shex-msft]

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

[Greg_Deckler]

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

[hannes86]

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