Show value from 1 or the other table based on filter of a third table

[JoycevanWijk]

I have three tables. 

1: mutations

2: cost1

3: cost2

All three tables have 1 column in common: costnumber. There is a one to many relationship between mutations and the other two columns.

The costnumber values in costs1 and costs2 are the same, but the description in both columns is different. 

So you have A, B and C in both columns. But in one table A = white, B = black, C = orange, and in the other table A = green, B = blue, C = yellow. 

I want to show either the descripton of cost1 or cost2 based on a filter on the column "administration" in the table mutations. This discription will be in a matrix with the sum of another column in mutations. 

I think I have to do something with RELATED and VAR, but I haven't gotten it to work yet. 

Any help is greatly appreciated.

[JoycevanWijk]

Hi, I understand your question. But sadly I don't have any sample data that I can show. I have tried to explain it as good as I can. I'm sorry I have not succeeded in explaining it correctly. 

[gmsamborn]

Sample data can be as simple as what you have described.  It just makes it easier than me trying to guess at what your tables look like.

Only a couple of rows and the expected result is fine even if the data is as simple as colors.

[JoycevanWijk]

I truly understand, but I don't know how to create that in a quick way in power bi and since its almost 11 pm here it is bedtime. 🙂

[gmsamborn]

Sorry.  I meant in Excel.

Also, it would take you much less time that me if I'm unsure.

[JoycevanWijk]

Kostendrager_1

Kostendrager	Description
A	Black
B	Blue
C	Brown

Kostendrager_2

Kostendrager	Description
A	Red
B	Pink
C	Orange

Mutations

Kostendrager	Administratie	Bedrag
A	WL	€                        5,00
B	WL	€                        4,00
C	WL	€                        3,00
A	AT	€                        3,00
B	AT	€                        4,00
C	AT	€                        5,00

Visual table when filtered on WL

Kostendrager	Description	value
A	Black	€                        5,00
B	Blue	€                        4,00
C	Brown	€                        3,00

Visual table when filtered on AT

Kostendrager	Description	value
A	Red	€                        3,00
B	Pink	€                        4,00
C	Orange	€                        5,00

[gmsamborn]

Hi @JoycevanWijk 

Would a measure like this help?

_Description = 
VAR _Admin = SELECTEDVALUE( 'Mutations'[Administratie] )
VAR _Logic =
    IF(
        _Admin = "WL",
            CALCULATE(
                MAX( 'Kostendrager_1'[Description] ),
                CROSSFILTER( Kostendrager_1[Kostendrager], Mutations[Kostendrager], Both )
            ),
        "AT"
    )
RETURN
    _Logic

or simply:

_Description = 
    IF(
        SELECTEDVALUE( 'Mutations'[Administratie] ) = "WL",
            CALCULATE(
                MAX( 'Kostendrager_1'[Description] ),
                CROSSFILTER( Kostendrager_1[Kostendrager], Mutations[Kostendrager], Both )
            ),
        "AT"
    )

Joyce.pbix

I'm hoping that is what you meant.

[JoycevanWijk]

I'm so sorry, my last table was wrong. It should not say AT, but the colors from kostendrager_2 description. I changed my reply. 

[gmsamborn · Accepted solution]

Try this.

Joyce.pbix

[gmsamborn]

It seems we were replying to each other at the same time.

I'm glad it worked.

[gmsamborn]

I'm just going by the table labeled "Visual table when filtered on AT" in Message 7 of 9.  It simply shows "AT" for the description.

Please go back and read Message 2 of 9.

[gmsamborn]

Hi @JoycevanWijk 

I would like to help but I'm a little unclear about your requirements.

Can you show the following?

1)  Please provide sample data that covers your issue or question completely.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...

2) Please show the expected outcome based on the sample data you provided.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

3) Please explain how you would expect to get from step 1 to 2.

4) If possible, please show your past attempts at a solution.

[JoycevanWijk]

Got it working! With your help. 

_Description =

VAR _Admin = SELECTEDVALUE( 'Mutations'[Administratie] )

VAR _Logic =

    IF(

        _Admin = "WL",

            CALCULATE(

                MAX( 'Kostendrager_1'[Description] ),

                CROSSFILTER( Kostendrager_1[Kostendrager], Mutations[Kostendrager], Both )

            ),CALCULATE(

                MAX( 'Kostendrager_2'[Description] ),

                CROSSFILTER( Kostendrager_2[Kostendrager], Mutations[Kostendrager], Both )

            )

    )

RETURN

    _Logic