Unable to get correct values in power bi

[nishthabhakta20]

Hi all,

I am trying to make a measure which reacts on a date slicer. This date slicer comes from a DateTable which is the main date table in my data model. The measure calculates the total inventory for all pole type. When I broke down the measure which selected pole type from Master Summary, I was able to see correct values. The master summary and master inventory both have date columns which are related to date column in main date table via many to one relationship. Master Summary and Master Inventory are also connected via Pole Type. When I use the date slicer, all I get is 0 even though I have values corresponding to each pole. Here is my formula: 

1st of month inventory test4 = VAR SelectedDate99 = SELECTEDVALUE('Query1'[Date])

RETURN

IF(

    NOT ISBLANK(SelectedDate99),

    CALCULATE(

        SUM('Master Inventory'[Total Monthly Inventory]),

        'Master Inventory'[Date] = SelectedDate99,

        TREATAS(VALUES('Master Summary (Core + Non-Core)'[Pole Type]), 'Master Inventory'[Pole Type])

    ),

    0

)

[Anonymous · Accepted solution]

Hi @nishthabhakta20 

The SelectedDate99 = SELECTEDVALUE（'Query1'[Date]) returns the (blank), that's why all you get is 0.

Please try the aggregation functions like max, min to extract the current value(not text), they are just as valid as selectedvalue in this case

^{Best Regards,}

^{Zhengdong Xu}

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

[nishthabhakta20]

Hi Daniel, 

Thank you for the input. However, I have entry month wise. And everything is already calucated in master inventory table. I feel there is some issue where the date table is not able to link dates with master inventory table. I used your solution but I get the error in image which I am suppose to get as one pole will have one inventory value for one month 

[Daniel29195]

@nishthabhakta20 

if you are using this measure in a visual without using the date column , it will always returns 0 ,  unless you specify one day. 

the reason is that you have more than 1 date value in the filter context, selectedvalue will return blank() .

so if you are filtering on 1 month, ( more than 1 day ) ,  since selectedvalue cant return 1 value, it will return blank() .

try changing your code to this : 

1st of month inventory test4 = VAR SelectedDate99 = values('Query1'[Date])

RETURN
IF(
    NOT ISBLANK(SelectedDate99),
    CALCULATE(
        SUM('Master Inventory'[Total Monthly Inventory]),
        'Master Inventory'[Date] in SelectedDate99,
        TREATAS(VALUES('Master Summary (Core + Non-Core)'[Pole Type]), 'Master Inventory'[Pole Type])
    ),
    0
)

let me know if that works .

If this answers your question  ,  mark it as the solution ✅ so can you can help  other people in the community find it easily .

[nishthabhakta20]

In my visual I am using Date column from my main date table