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

[MrSam]

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

[FreemanZ · Accepted solution]

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. 

[tamerj1]

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 

[FreemanZ · Accepted solution]

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. 

[tamerj1 · Accepted solution]

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] ) )
)

[MrSam]

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.

[tamerj1]

 @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]
)

[FreemanZ]

hi @tamerj1 

that is what i was striving for. thank you.