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
Krish_76
Frequent Visitor

Legend is not showing correctly on filled map

Hi

 

I have been working on model in which i am using a filled map to show the variance between current year and last year on the states. Everything is working fine but when i drop a data filed on to the legend column it is messing up my map colors

 

Here is an example:

 

Created a sample data in Excel (Didn't know how to attach the document so i am placing the values here)

 

statedatecur_yearpy_year
az1/1/20172015
az1/2/201756
az1/3/20171020
az1/4/20172535
az1/5/201710090
ca1/1/20171211
ca1/2/20171112
ca1/3/20173020
ca1/4/2017515
ca1/5/2017155
co1/1/20172030
co1/2/20173040
co1/3/20171516
co1/4/20171610
co1/5/2017104

 

I uploaded this data in to Power BI and created 2 custom columns and i aslo created a date table with in the power BI and also unique States table

 

Custom Columns:

 

Custom 1.JPGCustom 2.JPG

 

So finally this is how the model is looking before i drop the legend column

Before_Legend.JPG

 

On the Map when we move the mouse to those states it will be showing the State name and teh average of "diff" column

For example for the state AZ : the average "diff" is -1.20 and the data colors should be between yellow to red 

Data Colors :

 

data colors.JPG

 

Now when i drop the Legend column into the Legend filed then the colors are changing and the average values are also changing when i move the mouse to each state

 

After moving Legend into Legend column:

 

After_Legend.JPG

It showing the Avg Diff as -10.00 instead of -1.20.

 

And i also i found something intresting, right now i clicked only year but when i click a particular day then the legend is working fine.

 

Legend with day.JPG

 

Don't know why it creating a problem when i select for a compelte year/month when i drag the legend column into the legend field. I want to use the legend because when i click on neutral it should highlight those states where the Avg diff is between -3 and +3 

 

 Table relations

 

TR.JPG

 

 

 After commenting 

Jared It didn't give me an option to attach the screenshot

Pelase find hte screenshot over here

after_comment.JPG

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hey @Krish_76, so it is not possible to combine color saturation and a legend, because it introduces 2 conflicing uses of color. Power BI will use the legend settings and ignore the color saturation if you try to do both. You will need to choose between using color saturation without a legend, or using a custom color to create buckets, and setting the colors for those buckets.

 

For the second option, you would need to adjust the formulas slightly:

DiffColumn = CALCULATE ([Diff_test],ALLEXCEPT(MAP_Test,MAP_Test[state]))

Legend = IF ( MAP_Test[DiffColumn]>0, "Positive",IF(MAP_Test[DiffColumn] =0 ,"Neutral",IF(MAP_Test[DiffColumn]<0,"Negative")))

 

Then you can put Legend on the legend field. You can then change the data colors for your 3 legend values, so you could set Positive, Negative, and Neutral to the colors you want.

 

To give more granularity, you could consider doing buckets for ranges (0-5, 5-10, etc) rather than Pos/Neg/Neut

View solution in original post

8 REPLIES 8
Krish_76
Frequent Visitor

Can anyone help me on this
Anonymous
Not applicable

Hi @Krish_76, the issue is with your conditional column. For AZ, you have different values for the conditional column for each day. This is why the legend is not working until you select a day.

 

I think what you want is actually measure called Diff that calculates the average diff

 

Diff = AVERAGE ( [diff] )

 

You can then make a calculated column that calculates the Diff measure for each state across every day.

 

DiffColumn = CALCULATE ( Diff , ALL ( Day )

 

Finally, you can make your legend column as

 

Legend = IF ( DiffColumn > 0, "Positive", IF ( DiffColumn = 0, "Neutral", IF ( DiffColumn < 0, "Negative")))

 

 

Let me know if that works!

@Anonymous I couldn't find an option to the attach the screenshot to the reply But i posted the screenshot to my original post, you can find it at the bottom.
Anonymous
Not applicable

Can you post your .pbix file? It would be helpful to look at the data model. Can you share the calculation you are using for DiffColumn?

@Anonymous Here is the file: https://mega.nz/#!H5xC2ZqZ!r2n4cA63-YZPjAi_JRHFcMctDfsUkhBauAMO4wCwmUo Here are the measure and calc columns that i have used Diff_test = AVERAGE(MAP_Test[diff]) DiffColumn = CALCULATE ([Diff_test],ALL('date'[Date_day])) Error coming on legend Legend = IF ( MAP_Test[Diff_test]>0, "Positive",IF(MAP_Test[Diff_test] =0 ,"Neutral",IF(MAP_Test[Diff_test]<0,"Negative")))
Anonymous
Not applicable

Hey @Krish_76, so it is not possible to combine color saturation and a legend, because it introduces 2 conflicing uses of color. Power BI will use the legend settings and ignore the color saturation if you try to do both. You will need to choose between using color saturation without a legend, or using a custom color to create buckets, and setting the colors for those buckets.

 

For the second option, you would need to adjust the formulas slightly:

DiffColumn = CALCULATE ([Diff_test],ALLEXCEPT(MAP_Test,MAP_Test[state]))

Legend = IF ( MAP_Test[DiffColumn]>0, "Positive",IF(MAP_Test[DiffColumn] =0 ,"Neutral",IF(MAP_Test[DiffColumn]<0,"Negative")))

 

Then you can put Legend on the legend field. You can then change the data colors for your 3 legend values, so you could set Positive, Negative, and Neutral to the colors you want.

 

To give more granularity, you could consider doing buckets for ranges (0-5, 5-10, etc) rather than Pos/Neg/Neut

Everything is working fine now Thanks @Anonymous .

@Anonymous Thanks for looking into the issue. I followed the same steps and now it showing the average correctly on the map But when i am creating the caluculated column it for Legend it's giving me an error saying "A circular dependency was detected:" Attached Screenshot

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.