Forum Discussion

Riggan2023's avatar
Riggan2023
Frequent Visitor
3 years ago

Dynamic totals counting in calculated column

Hi. I need help vith creating a dinamic total calculations in my calendar table. 

So I have a table called Dataset consisted of  foloving columns: ArchDate, Type, Status. 

And of caurse I can use slicers to filter it by Status or Type.

Ive created a Calendar table using DAX 

MyCalendar = CALENDAR(DATE(2020,01,01),TODAY())

 

Then I've added calculated column "Totals" which counts a number of occurances of each date in Dataset[ArchDate] 

 

Totals = 
var total_Calc=CALCULATE(COUNTA('Dataset'[ArchDate]),FILTER('Dataset','Dataset'[ArchDate]=MyCalendar[Date]))
return if(total_Calc=BLANK(),0,total_Calc)

 

And now my calendar looks like this

 

But my problem is that I cant use slicers to filter values of Dataset. For example I need to count just First Stage but if I select the value of the Typeslider values of MyCalendar[Totals] remains the same. 

 

 

Please advise how to resolve it. Is it possible? This is my sample pbix https://file.io/Hk0hc48YCOfi 

 

Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    Riggan2023 , Create a measure like

     

    Totals =
    var total_Calc=CALCULATE(COUNTA('Dataset'[ArchDate]),FILTER('Dataset','Dataset'[ArchDate]=related(MyCalendar[Date]))
    return if(total_Calc=BLANK(),0,total_Calc)

     

     

    I think this should work too

     

    Totals = CALCULATE(COUNTA('Dataset'[ArchDate]))+0

    • Riggan2023's avatar
      Riggan2023
      Frequent Visitor
      3 years ago

      I've tried both your suggestions:

      Totals = 
var total_Calc=CALCULATE(COUNTA('Dataset'[ArchDate]),FILTER('Dataset','Dataset'[ArchDate]=related(MyCalendar[Date])))
return if(total_Calc=BLANK(),0,total_Calc)

      This shows me that MyCalendar[Date] does not exists.

      The second code counts same totals for each row