Marguie

[Syndicate_Admin]

I need help with a visualization in power bi, I have a table with the date and the goal defined for that month and I have a KPI visual. I have a date filter and the visual changes when I choose one month or another, but if what I choose is a day or week it appears blank, you can help me

[Anonymous]

Hi @Syndicate_Admin ,

According to your description, here are my steps you can follow as a solution.

(1) This is my test data. 

(2) We can create a measure. 

Flag = IF(ISFILTERED('Table'[Date].[Day])=TRUE(),0,1)

(3) Place [Flag=1] on visual object screening and then the result is as follows.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[Syndicate_Admin · Accepted solution]

In the end I did it instead of with a measure, with a calculated column Monthly Goal = VAR Date = My Table[Date] RETURN CALCULATE(FIRSTNONBLANK(Goals[Goal],Goals[Goal] ), FILTER(Goals, (date > Goals[Date] & date <= EOMONTH(Goals[Date], 0))))

[C4YNelis]

Hi @Syndicate_Admin ,

good to hear you managed to solve the issue. I'm not sure it's the best way to solve this, but if it works for you, then by all means.

Be wary of circular dependencies though when using calculate in calculated columns!

Avoiding circular dependency errors in DAX - SQLBI

Cheers

[C4YNelis]

Hi @Syndicate_Admin ,

it's a little difficult to say for sure without seeing your data and model, however, it sounds like you have a target for the month and you are trying to show this on a different granular level from what you have modelled.

Basically it all depends on how you have linked your targets to your calandar table. You could have linked the month to one specific date (e.g. the first or the last date of the month), since you're trying to see targets for one day. If so, that means that selecting a random (few) different day(s) for that month, without overlapping your linked target date, will give you a blank target, since there is no matching target for that specific day.

Another possibility is that you explicitely modelled the monthly target as such by linking to a month (which I would not recommend), but then there is no logic in selecting just a day (since that is on a much finer granular level than your targetdefinition). Selecting a month might also total the target times the number of days in that month, unles you are using for instance a min or max function.

My advice would be to recalculate your target to a granular level where you have a daily target available and link that to your calandar table.

Hopefully this makes sense.

Cheers