topic Sum from results of IF / AND Measure in DAX Commands and Tips

Sum from results of IF / AND Measure

ddent1406 — Tue, 10 May 2022 09:23:09 GMT

Good Morning..

I've written a a quick DAX statement to give me the results i need on line by line basis

C_TerminalsNotTagging = If(AND(CALCULATE(FACT_AML_SSBT_DAILY_SESSIONS_DATA[NickNameCount])<1,Count(__DIM_Terminals[Dim_Terminal_Key]) >0),1,0)

Which gives me the output below..

What i would now like to do is either sum or count the 1 in that column to give me the total of 2.. However, either i'm having a really bad day or this is more difficult than i 1st thought..

Any help would be appreciated.

Re: Sum from results of IF / AND Measure

SpartaBI — Tue, 10 May 2022 11:00:41 GMT

ddent1406 the general answer is to wrap your measuere with SUMX.
The important thing is what to put in the first statement and that is depandant on your model and desired visual.
For a specific visual you need to put all the columns that play affect in the matrix rows, and if they come from different tables you need to use for example summarize like what I wrote.
Hard to say without seeing your model and which are the relevant columns in your matrix / table visual but I wrote here the general code for that and see if you can easily adjust to your column names:

C_TerminalsNotTagging = SUMX( SUMMARIZE ( FACT_AML_SSBT_DAILY_SESSIONS_DATA, Dim1[Column 1], Dim2[Column 2], ... , ... , ... , FACT_AML_SSBT_DAILY_SESSIONS_DATA[Column3] ), If(AND(CALCULATE(FACT_AML_SSBT_DAILY_SESSIONS_DATA[NickNameCount])<1,Count(__DIM_Terminals[Dim_Terminal_Key]) >0),1,0) )

Showcase Report – Contoso By SpartaBI

Re: Sum from results of IF / AND Measure

ddent1406 — Tue, 10 May 2022 12:55:29 GMT

Hi SpartaBI ,
worked great , had to chnage the if statement around a bit.. but got the result i wanted.

Re: Sum from results of IF / AND Measure

SpartaBI — Tue, 10 May 2022 12:58:44 GMT

ddent1406 my pleasure 🙂

Showcase Report – Contoso By SpartaBI