Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
hiran
Helper I
Helper I

Returning a specific value from another table with a one to many relationship

hello!

this is the first table:

quote id:sales
a100
b200
c300

 

this is the 2nd table:

quote id:countrycontinent
aenglandeurope
aspaineurope
bgermanyeurope
bindiaother
csouth africaother

 

so what i want to do is add a column to the first table that says if the continent is europe and there is only 1 country in europe then return the country for that quote id that is in europe and if there is more than 1 country that is in europe for a given quote id (eg a) then return "more than one" and if it just says other then return "other".

 

in my head table 1 would then look like this:

quote idsalescountry
a100More than one
b200Germany
c300Other

in my head it makes sense but i'm not sure how to do it in power bi. i'd imagine it is possible but for the life of me i can't figure out what i'm missing

9 REPLIES 9
Ashish_Mathur
Super User
Super User

Hi,

Quote ID B has 2 continents - Europe and Other.  Shouldn't the answer be Other?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi @Ashish_Mathur ,

 

Pls note:

there is only 1 country in europe then return the country for that quote id that is in europe

 

That's why it returns Germany.

 

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

amitchandak
Super User
Super User

@hiran , Create a new column like

 

new column =
var _1 = countx(filter(Table2, Table2[quote id] = Table1[quote id] ), Table2[country])+0
var _2 = Maxx(filter(Table2, Table2[quote id] = Table1[quote id] ), Table2[continent])
var _3 = Maxx(filter(Table2, Table2[quote id] = Table1[quote id] ), Table2[country])
return
Switch(True() ,
_2 = "europe" && _1 >1 , "More than 1 country",
_2 = "europe" && _1 =1 , _3,
"Other"
)

thanks for the help! what you've said makes sense to me but i'm getting an error saying that i cannot convert country of type text to type number @amitchandak 

Hi @hiran ,

 

Create a measure as below:

Measure =
VAR _quoteids1 =
    CALCULATETABLE (
        VALUES ( 'Table (2)'[quote id] ),
        FILTER ( ALL ( 'Table (2)' ), 'Table (2)'[continent] = "Europe" )
    )
VAR _quoteids2 =
    CALCULATETABLE (
        VALUES ( 'Table (2)'[quote id] ),
        FILTER ( ALL ( 'Table (2)' ), 'Table (2)'[continent] = "Other" )
    )
VAR _countcountry =
    CALCULATE (
        DISTINCTCOUNT ( 'Table (2)'[country] ),
        FILTER (
            ALL ( 'Table (2)' ),
            'Table (2)'[continent] = "Europe"
                && 'Table (2)'[quote id] = MAX ( 'Table (2)'[quote id] )
        )
    )
RETURN
    IF (
        MAX ( 'Table (2)'[quote id] ) IN _quoteids1,
        IF (
            _countcountry = 1,
            CALCULATE (
                MAX ( 'Table (2)'[country] ),
                FILTER (
                    ALL ( 'Table (2)' ),
                    'Table (2)'[continent] = "Europe"
                        && 'Table (2)'[quote id] = MAX ( 'Table (2)'[quote id] )
                )
            ),
            "More than one"
        ),
        IF (
            MAX ( 'Table (2)'[quote id] )
                IN _quoteids2
                    && NOT ( MAX ( 'Table (2)'[quote id] ) IN _quoteids1 ),
            "Other"
        )
    )

And you will see:

vkellymsft_0-1630311707141.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

Thanks @v-kelly-msft! It sort of works but I'm having a problem and I can't seem to find out why. In the .pbix file you attached when removing the quote id the table stays the same so it looks like below and that is the end table i wanted. i didn't know that i could do this creating a measure which is why i had originally asked for a calculated column instead so it's good to know that i could do this using a measure:

MeasureSales
More than one100
Germany200
Other300

 

But when i do it in my file with the exact same measure I get the following table:

CountrySales
Germany600

 

would you know why that is? 

Hi  @hiran ,

 

Are you doing an aggregation in the field of the column "sales" of the table visual?

I tested by the sample data ,if the column is aggregated,then it will return only one row,as show below:

vkellymsft_0-1630632407669.png

If you choose "Dont summarize",you will get the required output:

vkellymsft_1-1630632462430.png

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

ah thanks @v-kelly-msft . another question for you then (sorry i probably missed something). i changed it so it doesn't summarise but then now it lists each country separately presumably because there's different sales value for each country. so like the table below

countrysale
germany100
spain200
germany600
more than one

800

more than one

100

spain 700
other

900

other 

200

i'd want it so the sales are grouped for the country instead of listed separately if that makes sense, so like below:

countrysales
spain900
germany700
more than one900
other1100

 

again sorry because it would have helped if i originally specified this in the question. this is wh i thought it wasn't possible to have the countries as a measure so which is why i tried to do it as a calculated column instead

Hi  @hiran ,

 

Create a column as below:

column =
VAR _quoteids1 =
    CALCULATETABLE (
        VALUES ( 'Table (2)'[quote id] ),
        FILTER ( ALL ( 'Table (2)' ), 'Table (2)'[continent] = "Europe" )
    )
VAR _quoteids2 =
    CALCULATETABLE (
        VALUES ( 'Table (2)'[quote id] ),
        FILTER ( ALL ( 'Table (2)' ), 'Table (2)'[continent] = "Other" )
    )
VAR _countcountry =
    CALCULATE (
        DISTINCTCOUNT ( 'Table (2)'[country] ),
        FILTER (
            ALL ( 'Table (2)' ),
            'Table (2)'[continent] = "Europe"
                && 'Table (2)'[quote id] = EARLIER ( 'Table (2)'[quote id] )
        )
    )
RETURN
    IF (
        'Table (2)'[quote id] IN _quoteids1,
        IF (
            _countcountry = 1,
            CALCULATE (
                MAX ( 'Table (2)'[country] ),
                FILTER (
                    'Table (2)',
                    'Table (2)'[continent] = "Europe"
                        && 'Table (2)'[quote id] = EARLIER ( 'Table (2)'[quote id] )
                )
            ),
            "More than one"
        ),
        IF (
            'Table (2)'[quote id]
                IN _quoteids2
                && NOT ( 'Table (2)'[quote id] IN _quoteids1 ),
            "Other"
        )
    )

And you will see:

vkellymsft_0-1630921694794.png

Check my updated .pbix file attached.

 

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.