cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
NilR
Helper III
Helper III

Measure in If statement returns incorrect total

I am new in PBI and desperately need help. I have a summarized table and the end result is the last column ( had_well) in the picture, but returns the wrong total which is the first column total. I understand that I need to iterate through ID ( first column in pic) to get right result, but I don't know how.

 

 

 

VAR __tmpTable = 
ADDCOLUMNS(
        SUMMARIZE(
            'AW',
            'AW'[ID],
            "MAXID", CALCULATE(DISTINCTCOUNT('AW'[ID]), ALLEXCEPT('AW','AW'[ID]),'AW'[DATE]=_maxdate)
        ),"COUNTYR1", [YR1_HAD_WELL],"COUNTYR2", [YR2_HAD_WELL])

Return
IF(HASONEVALUE('AW'[M18]),SUMX(FILTER(__tmpTable,[MAXID]=1),[COUNTYR2]),SUMX(FILTER(__tmpTable,[MAXID]=1),[COUNTYR1]))

 

 

 

I also tried this but returned blank total:

 

 

Return
 IF(HASONEFILTER([ID]),SUMX(FILTER(__tmpTable,[MAXID]=1),(IF(_m18="YES",[COUNTYR2],[COUNTYR1]))))

 

 

 

The if statement returns the first column total.

 

 

Thank you!

1 ACCEPTED SOLUTION
NilR
Helper III
Helper III

I changed the calculation and worked.

 

ADDCOLUMNS(
        SUMMARIZE(
            ''AW'',
            ''AW''[ID],''AW''[M18],
            "MAXID", CALCULATE(DISTINCTCOUNT(''AW''[ID]), ALLEXCEPT(''AW'',''AW''[ID]),''AW''[DATE]=_maxdate)
        ),"YR1", [YR1_HAD_WELL],"YR2", [YR2_HAD_WELL],"TTL_MO", [TTL_MO],"HAD_WELL",IF([M18]="YES" ,[YR2],[YR1]))
Return
SUMX(FILTER(__tmpTable,[MAXID]=1),[HAD_WELL])

 

View solution in original post

6 REPLIES 6
parry2k
Super User III
Super User III

@NilR That's' pretty good news what can be better than this. Thanks for letting me know and posting your solution.

 

Cheers!!






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





NilR
Helper III
Helper III

I changed the calculation and worked.

 

ADDCOLUMNS(
        SUMMARIZE(
            ''AW'',
            ''AW''[ID],''AW''[M18],
            "MAXID", CALCULATE(DISTINCTCOUNT(''AW''[ID]), ALLEXCEPT(''AW'',''AW''[ID]),''AW''[DATE]=_maxdate)
        ),"YR1", [YR1_HAD_WELL],"YR2", [YR2_HAD_WELL],"TTL_MO", [TTL_MO],"HAD_WELL",IF([M18]="YES" ,[YR2],[YR1]))
Return
SUMX(FILTER(__tmpTable,[MAXID]=1),[HAD_WELL])

 

View solution in original post

parry2k
Super User III
Super User III

@NilR throw some sample data and make sure respective tables are in the data model with the relationship.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





parry2k
Super User III
Super User III

@NilR oh man there is a lot going on, and very hard to decode it. better you put a sample pbix file and explain what you are looking for and will able to help. I cannot spend time figuring this measure out. sorry about that.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





I have big table but I will see what I can do. In the meantime I changed my code and made it smaller. hopefully make more sense now. 🙂

parry2k
Super User III
Super User III

@NilR try something like this

 

Measure = 
SUMX ( vALUES ( Table[Id] ), 
IF(SELECTEDVALUE('0410 Adult Wellness'[MALE_18-34]) = "YES" , __tmpYR2, __tmpYR1)
)

 

Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors
Users online (856)