No results

[3s]

Hi, I am new in here and in Dax. I couln't find a similar question, so hopefully someone can help.

I am trying to achieve the same as Excel: countif(B:B;B2;A:A;D2).

This is what I am doing:

Count =

VAR vB = SELECTEDVALUE('Table'[ID])

VAR vD = SELECTEDVALUE('Table'[Year+1])

RETURN

COUNTROWS (

    FILTER (

        ALL ( 'Table' ),

        'Table'[ID] = vB

            && 'Table'[Year] = vD

    )

)

Unfortunately there are no results.

What am I doing wrong?

[krishnakanth240 · Accepted solution]

Hi @3s 

Have tried this in desktop with your sample data. Please let me know the exact requirement/logic if it does not work.

[3s]

Thanks @Ashish_Mathur and @krishnakanth240 both your solutions worked out for me!

[v-aatheeque]

Hi @3s 

Following up to confirm if the earlier responses addressed your query. If not, please share your questions and we’ll assist further.

[krishnakanth240 · Accepted solution]

Hi @3s 

Have tried this in desktop with your sample data. Please let me know the exact requirement/logic if it does not work.

[3s]

Now I am getting results. Unfortunately it is not wat I am looking for.

I want to calculate the number of starting and stopping members. 

The columns 'year+1' and 'year-1' are calculated with column 'year'.

The columns 'also member following year' and 'was member last year' are the columns I am looking for a DAX-formula.

In Excel it would be for 'also member following year': countifs(B:B;B2;A:A;C2)

And for 'was member last year': countifs(B:B;B2;A:A;E2)

If there are other options I am interested as well.

Column A	Column B	Column C	Column D	Column E	Column F
year	member	year+1	also member following year	year-1	was member last year
2016	K58PR5Q	2017	0	2015	0
2018	K58PR5Q	2019	1	2017	0
2019	K58PR5Q	2020	1	2018	1
2020	K58PR5Q	2021	1	2019	1
2021	K58PR5Q	2022	1	2020	1
2022	K58PR5Q	2023	1	2021	1
2023	K58PR5Q	2024	1	2022	1
2024	K58PR5Q	2025	1	2023	1
2025	K58PR5Q	2026	0	2024	1
2022	K65RR1W	2023	1	2021	0
2023	K65RR1W	2024	1	2022	1
2024	K65RR1W	2025	1	2023	1
2025	K65RR1W	2026	0	2024	1
2018	K36GO4B	2019	0	2017	0
2020	K36GO4B	2021	0	2019	0
2022	K36GO4B	2023	0	2021	0
2024	K36GO4B	2025	0	2023	0
2019	K33AM4I	2020	1	2018	0
2020	K33AM4I	2021	1	2019	1
2021	K33AM4I	2022	0	2020	1
2025	K33AM4I	2026	0	2024	0
2022	K33QZ4N	2023	0	2021	0
2023	K33RX7X	2024	0	2022	0
2025	K34AN4J	2026	0	2024	0
2025	K35HR4A	2026	0	2024	0
2024	K52XJ8M	2025	0	2023	0
2021	K33WA1O	2022	0	2020	0
2017	K32SV1D	2018	0	2016	0
2016	K34DF6S	2017	0	2015	0
2019	K35UQ2U	2020	0	2018	0

[Ashish_Mathur · Accepted solution]

Hi,

These calculated column formulas work

=CALCULATE(COUNTROWS(Data),FILTER(Data,Data[member]=EARLIER(Data[member])&&Data[year]=EARLIER(Data[year+1])))+0

=CALCULATE(COUNTROWS(Data),FILTER(Data,Data[member]=EARLIER(Data[member])&&Data[year]=EARLIER(Data[year-1])))+0

Hope this helps.

[ryan_mayu]

@3s 

maybe you can try this to create a calculated column.

Count =
CALCULATE(
COUNTROWS('Table'),
FILTER(
'Table',
'Table'[ID] = EARLIER('Table'[ID])
&& 'Table'[Year] = EARLIER('Table'[Year])
)
)

if this does not work, could you pls provide some sample data and expected output?

could you pls provide some sample data and expected output?

[3s]

Thanks. Unfortunately I am getting the message about a circulair dependency detected: Table[Count]

[JamieHolding]

Count =

VAR vB = SELECTEDVALUE('Table'[ID])

VAR vD = SELECTEDVALUE('Table'[Year+1])

RETURN

IF(

 NOT(ISBLANK(vB)) && NOT(ISBLANK(vD)),

 COUNTROWS(

 FILTER(

 ALLEXCEPT('Table', 'Table'[ID], 'Table'[Year+1]),

 'Table'[ID] = vB && 'Table'[Year] = vD

 )

 ),

 BLANK()

)

It's a little hard to test without the data but can you try this?