Displaying a value if the use relationship value is blank

[stino123]

Hey,

I have a situation where I have net sales running via Shopify and we have targets stored in a dataflow. For a certain store there are a number of days where revenue was equivelant to zero however we do have a target set for that day. when loading it into a table the table will not show a target value on days where revenue was equivelant to zero. However the sum total does show the correct amount.

I can create a function that says if(rev=blank(),0,rev) however then the target value still does not load. 

The function for target is as followed: 

Target = CALCULATE(SUM(Targets[Net Sales]),USERELATIONSHIP(Targets[Store],FACT_Orders[Location]))

Please let me know if I am missing something.

[Adamboer]

It seems that the issue here is related to the fact that the table is not showing the target value on days where revenue was equivalent to zero. One way to solve this could be to modify the formula for the target value to include a condition that checks for days where the revenue was zero. Here's an example of how the formula could be modified:

Target = IF(CALCULATE(SUM(FACT_Orders[Revenue]),USERELATIONSHIP(Targets[Store],FACT_Orders[Location]))=0, SUM(Targets[Net Sales]), CALCULATE(SUM(Targets[Net Sales]),USERELATIONSHIP(Targets[Store],FACT_Orders[Location])))

This formula includes an "IF" statement that checks whether the revenue for a particular day is zero. If it is, the formula will return the sum of the target values. Otherwise, it will return the calculated target value based on the relationship between the store and the orders.

Alternatively, you could also try modifying the data model to include a table that has all possible dates for the period being analyzed, and then use this table as the basis for the calculation of both revenue and targets. This would ensure that all dates are included in the table, even those where revenue was zero.

As a side note, ProjectPro365 is a great tool that can help you with data modeling and data visualization in Power BI. It may be worth checking out if you are interested in optimizing your Power BI reports.

[stino123]

Hey @Adamboer , thanks for contributing. the suggested dax unfortunately does not work because the true condition would only work if there was one store if I did sume of all target net sales it takes all stores. I do use a custom table via a blank script that has every single day and that is the current active relationship. however it will not display blank days for whatever reason.