Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Dax Logic

Hallo All,

 

I have an interesting question and I hope someone can answer this.

 

DISTINCTCOUNT('Date'[MonthinCalender]), where the Column I am counting is just a month column in a date table. If one puts this measure over the month column you get a count of 1 for each month, which makes sense, if one puts this over quarters, one gets 3 over every full quarter and maybe a 1 or 2 at the start or end or you selected date range depending on whether it includes a full of partial quarter. But, because I wanted the count to return 3 always when reporting over quarters, I decided to change the measure as follows,

 

CALCULATE(MAXX(SUMMARIZE('Dates' , 'Dates'[MonthInCalendar] , "Column12" , DISTINCTCOUNT('Dates'[MonthInCalendar])) , [Column12]) , ALLSELECTED('Dates')). 
 
According to my logic, the maxx was suppose to calculate over allselected dates and return the max, so if it happens that there are only partial quarters at the start or end, these values wont be the max, because the max would be three. What one gets from this, is rather the distinctcount over all selected date, so I might aswell just used CALCULATE(DISTINCTCOUNT('Date'[MonthinCalender]), ALLSELECTED('Date)). Why would the logic of the dax measure calculate the distinctcount over allselected dates and not the maxx.
 
To visualize to you what I thought would happen. The summarize function will create a virtual table as follows;
Q1 1
Q2 3
Q3 3
Q4 2
Where the date selection was such that only one month and 2 months was include in the `1st and last quarter. Then the max function would look ove allselected dates and choose the max, so 3. But clearly the allselected calculate is effecting the Count and not the maxx. I dont have a problem with fixing this, I can write this fucntion in numerous ways to make it do what I want, but for the dax experts out there. What does the logic of my measure say that it is not giving me the intended solution?
Need Help

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Correction, in my summarize funciton I actually put the quarter and not the month. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    To give you an idea, the following measure does what I want; 

    MAXX(CALCULATETABLE('Dates' , ALLSELECTED('Dates')) , DISTINCTCOUNT('Dates'[MonthInCalendar]))
     
    I am just curious to know why my measure above does not do what I intend?
    • v-lid-msft's avatar
      v-lid-msft
      Community Support
      6 years ago

      Hi Anonymous ,

       

       

       

      When generate a summarize table, the context “ALLSELECTED()” also Evaluated, so the summarize table should like following: (if select between 1st  March and 31th November)

       

       

       

      Then the MAXX function calculate the same biggest value 3 for each row, every row have the same result because the ALLSELECTED context. If you want to get the  value of Coulmn12 for current quarter, we need to filter the summarize table.


      Best regards,

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        I am not sure you understand my problem, because what you are explaining is what I expected the measure to do. It is exactly what I wanted, I wanted the maxx to evaluate over allselected so that it returns 3 always. But, this is not what my measure did, the allselected summed the total count so, in my case there are 10 quarters selected, so my measure returns 10 for each quarter selected. So it seems the allselected made the Maxx behave like a sumx over allselected dates.