DAX Function assistance

[EmiliaB_123]

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. 

[selimovd · Accepted solution]

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

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[EmiliaB_123]

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]

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

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[EmiliaB_123]

HI @selimovd ,

I have one last question. I tried out the formula with the time reference as well and now I don´t get any values.

My DIM_Date table looks like this:

What could be the problem?

Thanks.

[selimovd]

Hey @EmiliaB_123 ,

I would guess it's the relationship between the date table and the fact table.

Can you do a double click on the relationship in the relationship view and check if they are connected by the right column?

Best regards

Denis

[EmiliaB_123]

Hi @selimovd ,

looks good to me:

[selimovd]

@EmiliaB_123  also looks good for me. I could take a look into the file if it doesn't contain confidential information or if you could remove sensitive data.
Otherwise it's a little hard to say without investigating further.

[EmiliaB_123]

Hi @selimovd , I checked the definition of DATESINPERIOD and it seems that the output of this formula is a date. What I need is to consider the last year starting from today to count to rows, so the output should be a whole number. Is there maybe another formula that I could check?

Thanks.

[EmiliaB_123]

@selimovd I unfortunately can´t share the file. Taking the confidential data out of it would also mean to take data out of the formula this problem is referring to. I´ll try to check it by myself. Thank you.