Forum Discussion

EmiliaB_123's avatar
EmiliaB_123
Helper II
5 years ago
Solved

DAX Function assistance

Hello everyone,

I am struggling with a dax function. I have a column with zeros and ones as the column content, I need to count the zeros and the ones for a certain time period. With which dax formulas can this be done? I wanted to first count the zeros for the last 12 months and then count all positions for the last 12 months and then devide them to calculate a percentage value as a result.

 

Thanks for the support on this. 

Need Help
  • selimovd's avatar
    selimovd
    5 years ago

    Hey EmiliaB_123 ,

     

    you would create a measure for the zeros like this one:

    Zeros =
CALCULATE(
    COUNTROWS(myTable)
    myTable[ColumnWithZeroValues] = 0
)

     

    And a similar for the ones and a normal COUNTROWS without the filter for all values. Then you can use all of the measures in the selected time period and you get your result.

     

    If you need any help please let me know.
    If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
     
    Best regards
    Denis
     

9 Replies

  • selimovd's avatar
    selimovd
    Most Valuable Professional
    5 years ago

    Hey EmiliaB_123 ,

     

    you would create a measure for the zeros like this one:

    Zeros =
CALCULATE(
    COUNTROWS(myTable)
    myTable[ColumnWithZeroValues] = 0
)

     

    And a similar for the ones and a normal COUNTROWS without the filter for all values. Then you can use all of the measures in the selected time period and you get your result.

     

    If you need any help please let me know.
    If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
     
    Best regards
    Denis
     
    • EmiliaB_123's avatar
      EmiliaB_123
      Helper II
      5 years ago

      HI selimovd 

      thanks for the quick reply - this works very good thanks! The only part missing would be how to I consider the last twelve months in this formula as well? Should I enter FILTER(DIM_Period[MONTHS]-12?

      • selimovd's avatar
        selimovd
        Most Valuable Professional
        5 years ago

        Hey EmiliaB_123 ,

         

        I think FILTER is a more complicated approach. I would just use DATESINPERIOD.

        The formula would then look like this:

        Zeros =
CALCULATE(
    COUNTROWS(myTable)
    myTable[ColumnWithZeroValues] = 0,
    DATESINPERIOD (
            'DateTable'[Date],
            MAX ( 'DateTable'[Date] ),
            -1,
            YEAR
    )
)

         

        Be aware that the time intelligence functions need a proper date table. Check here how to create a date table if you don't have one:

        https://softcrylic.com/blogs/power-bi-for-beginners-how-to-create-a-date-table-in-power-bi/

         

        If you need any help please let me know.
        If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
         
        Best regards
        Denis