Forum Discussion
Helper IIIMap Visual & Displaying YoY Changes in Moving Averages
Hi,Dave1mo1 . I am glad to help you.
According to your description, you are experiencing a situation where a specific continent on the map is not populated when the measure does not calculate the corresponding value.
Your measure is working fine, but when there is no corresponding data for a continent from the previous day, it is not shown on the map visual object.
I think you should consider creating a first new measure without changing the original one, and process the data for it: if the corresponding continent doesn't have data from the previous day, set a default value for it (it should be empty).
Check the results of the original measure calculation, if not empty, it will display the results of the measure calculation, if it is empty, it can be used to distinguish between the other data of the default value (such as 0 or other placeholders) If the measure itself needs to be aggregated calculation, it is recommended to use 0, as not to affect the final aggregation of total results.
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Dave1mo1Hi, Carson.
Thanks for your response. It looks to me like the issue is caused by there being no data, at all, for these specific "ship to states" in the fact table on the dates that are blank on the map. For example, I updated the file to include yesterday's orders, and now Arkansas is filled, but North Dakota & Idaho are not. The root cause seems to be that there were no rows on the fact table with those corresponding Ship To locations on 6/25, even though the measures are clearly being calculated for these days. Below is an updated map for today.
Can you provide any other tips in regards to this issue? I've tried adjusting the measures to add "+0," but that doesn't seem to resolve the issue. It adjusts the "Total Units" column to be "0," but the state's conditional formatting is still blank, and the tool tip for "YoY% Change" doesn't show when I hover.
Hi,Dave1mo1
Thank you for your reply, and I'm sorry to say that the advice provided earlier didn't work very well.
In fact, as you said, the problem is in the data source itself: the DAX code is calculating null results because some continents don't have the previous day's data. This is not due to a programming error in the measure or calculate column you created, so it is tricky to work with data that is not available directly from power BI.
I recommend that you use the IF function or the switch function to set the result of an empty calculation to a default value such as -999 or some other unlikely (and highly visible) result for cases where there is no actual data from the previous day.
For the matrix or table in the system automatically aggregated Total value, you can use the ISINCOPE function to artificially modify the final Total calculation results.
like this:
Using IF with the ISINCOPE function
This will not affect your original calculation.
You can then individually set up measures with special default values in Map visual, e.g. if the measure has a special value of -999, it will be marked by displaying the area in a specific color.
The absence of data in the data source itself is tricky for trying to modify the expected results directly in power BI dektop, as it is a problem with the data itself and not with the code written
Looking forward to your reply!
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- Dave1mo1
Hi,
I know this is a delayed response - my apologies. I ended up creating a dimensional table that includes each of the regions, a column with yesterday's date, and a key for each of the dimensions that the map can be filtered on. It was kind of a backwards way to do it, but seems to be working in my testing.
Thanks again for the help.