Forum Discussion
issue with data model
This is the relationship you would like to use
But unfortunately your relationship is at DAY level. So your grouping by hour in your visuals won't change filtered rows on the Retail table, because all hours will refer to the same day, so your total hours is the total hours of the day. You should change the granularity of the relationship OR you can change your formula:
Select Sales = /*IF( SUM('Retail Pos'[netAmount])=BLANK();0; SUM('Retail Pos'[netAmount]) )*/ var val=SELECTEDVALUE('Select Date'[Hour]) var total=calculate(sumx('Retail Pos';'Retail Pos'[netAmount]);'Retail Pos'[Hour]=val) RETURN if(isblank(total);0;total)repeat the formula for all the other metrics. This is the result:
Need separate date, hour and timestamp tables.
This is the relationship you would like to use
But unfortunately your relationship is at DAY level. So your grouping by hour in your visuals won't change filtered rows on the Retail table, because all hours will refer to the same day, so your total hours is the total hours of the day. You should change the granularity of the relationship OR you can change your formula:
Select Sales =
/*IF(
SUM('Retail Pos'[netAmount])=BLANK();0;
SUM('Retail Pos'[netAmount])
)*/
var val=SELECTEDVALUE('Select Date'[Hour])
var total=calculate(sumx('Retail Pos';'Retail Pos'[netAmount]);'Retail Pos'[Hour]=val)
RETURN
if(isblank(total);0;total)
repeat the formula for all the other metrics. This is the result:
Sir, first of all thankyou for solving this out.
, how can i change the granularity of the relationship
i used you formula in bar chart its working for hour. but all other values were affected. and no data in day wise
Create separate Hours and Date tables.
Need separate date, hour and timestamp tables.