topic Re: DAX Column/measure to get No of occurrences based on conditions in DAX Commands and Tips

DAX Column/measure to get No of occurrences based on conditions

MrSam — Mon, 08 May 2023 04:38:35 GMT

Hi Community,

Need your expert help getting the 1 in the result column when either of Model output value 1

My ultimate goal is to show No of occurrences in a matrix visual.

ID	Name	Model	Model Date	Model Output	Result
44444	Pan	A	31-Dec-22	0	0
44444	Pan	B	31-Dec-22	0	0
44444	Pan	A	31-Mar-23	0	0
44444	Pan	B	31-Mar-23	0	0
55555	Lemoine	A	31-Dec-22	1	1
55555	Lemoine	B	31-Dec-22	0	1
55555	Lemoine	A	31-Mar-23	0	1
55555	Lemoine	B	31-Mar-23	0	1
66666	Paul	A	31-Dec-22	1	1
66666	Paul	B	31-Dec-22	1	1
66666	Paul	A	31-Mar-23	0	1
66666	Paul	B	31-Mar-23	0	1
77777	Nancy	A	31-Dec-22	1	2
77777	Nancy	B	31-Dec-22	1	2
77777	Nancy	A	31-Mar-23	1	2
77777	Nancy	B	31-Mar-23	0	2
88888	Peter	A	31-Dec-22	1	2
88888	Peter	B	31-Dec-22	1	2
88888	Peter	A	31-Mar-23	1	2
88888	Peter	B	31-Mar-23	1	2

My ultimate target is to get below matrix visual,

Thank you

Re: DAX Column/measure to get No of occurrences based on conditions

tamerj1 — Mon, 08 May 2023 06:12:10 GMT

Hi MrSam

please try

ResultMeasure =
SUMX (
VALUES ( 'Table'[Model Date] ),
CALCULATE ( MAX ( 'Table'[Model Output] ) )
)

the Number of Occurances that is present in your expected matrix visual would be the total column at the left of the matrix

Re: DAX Column/measure to get No of occurrences based on conditions

FreemanZ — Mon, 08 May 2023 06:38:50 GMT

hi tamerj1

I was trying hard to add the expected calculated column. do you have some good suggestion?

What i succeed so far is very cumbersome:

Result2 = SUMX( FILTER( ADDCOLUMNS( SUMMARIZE( data, data[ID], data[Model Date] ), "Output", MAXX( FILTER( data, data[ID]=EARLIER(data[ID]) &&data[Model Date]=EARLIER(data[Model Date]) ), data[Model Output] ) ), data[ID]=EARLIER(data[ID]) ), [Output] )

tried to simplify the code with CALCULATE, but with no success.

Re: DAX Column/measure to get No of occurrences based on conditions

tamerj1 — Mon, 08 May 2023 07:11:56 GMT

Hi FreemanZ

My understanding that perhaps MrSam have some kind of live connection and cannot create calculated columns. This is why I proposed a measure. MrSam please let me know if I misunderstood the requirement.

ResultMeasure = SUMX ( VALUES ( 'Table'[Model Date] ), CALCULATE ( MAX ( 'Table'[Model Output] ) ) )

Re: DAX Column/measure to get No of occurrences based on conditions

MrSam — Mon, 08 May 2023 12:28:55 GMT

In this case I do not have a live connection. Thank you and this solution works. However, I have a few other fields adding to the Matrix visual, therefore, having the Total column in the matrix visual was not the best, so I will select FreemanZ solution for this particular scenario.

Re: DAX Column/measure to get No of occurrences based on conditions

tamerj1 — Mon, 08 May 2023 13:35:05 GMT

MrSam

You can also try the following calculated column

Result = SUMX ( SUMMARIZE ( CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[ID] ) ), 'Table'[Model Date], "@Value", MAX ( 'Table'[Model Output] ) ), [@Value] )

Re: DAX Column/measure to get No of occurrences based on conditions

FreemanZ — Mon, 08 May 2023 14:06:11 GMT

hi tamerj1

that is what i was striving for. thank you.