Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
hello!
this is the first table:
quote id: | sales |
a | 100 |
b | 200 |
c | 300 |
this is the 2nd table:
quote id: | country | continent |
a | england | europe |
a | spain | europe |
b | germany | europe |
b | india | other |
c | south africa | other |
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 id | sales | country |
a | 100 | More than one |
b | 200 | Germany |
c | 300 | Other |
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
Hi,
Quote ID B has 2 continents - Europe and Other. Shouldn't the answer be Other?
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!
@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:
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:
Measure | Sales |
More than one | 100 |
Germany | 200 |
Other | 300 |
But when i do it in my file with the exact same measure I get the following table:
Country | Sales |
Germany | 600 |
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:
If you choose "Dont summarize",you will get the required output:
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
country | sale |
germany | 100 |
spain | 200 |
germany | 600 |
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:
country | sales |
spain | 900 |
germany | 700 |
more than one | 900 |
other | 1100 |
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:
Check my updated .pbix file attached.
Best Regards,
Kelly
Did I answer your question? Mark my reply as a solution!
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
118 | |
101 | |
71 | |
61 |