Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
sai1092
Frequent Visitor

Help with dynamic measure based on Criteria (IF not working)

Hi,

 

I'm trying to write a single measure to show Consolidated Amount Modified for any account starting with 1 and show the Average Amount for any account starting with 4 as shown below:

 

image.png

Measure = IF(LEFT(VALUES(Transactions[ACCOUNTNUMBER]),1)="1", [Consolidated_Amt_Modified], VALUES(Transactions[EXCH_AMT]))

 

I get the following error for the above formula

 

 
 

 

image.png

 

 

 

Consolidated Amount Modified is a measure that multiplies the SUM(Amount) * rate (from another table for the month selected):

 

Consolidated_Amt = SUM(Transactions[SUM(AMOUNT)])*Calculate(MAX('rate table'[CURRENT_RATE]),FILTER('rate table','rate table'[ACCTPRD]= MAX('rate table'[ACCTPRD])))
 
Consolidated_Amt_Modified = var tempTable =
SUMMARIZE(
Transactions,
Transactions[ACCOUNTNUMBER],
"NewColumn",[Consolidated_Amt])
return
SUMX(tempTable,[NewColumn])

 

AVG_AMOUNT is already calculated in the SQL.

 

I have attached the Power BI file download link  for your reference and happy to give further context on the data model to get this resolved.

 

https://drive.google.com/file/d/1fmktENvMSPJuRtMYb1U_zT4pRy6Q92ic/view?usp=sharing

 

Thank you

6 REPLIES 6
az38
Community Champion
Community Champion

hi @sai1092 

i couldn't check, but at first sight the issue is as you are creating the measure, you should try to pack your measures into either SELECTEDVALUE([Consolidated_Amt_Modified]) or CALCULATE(SUM([Consolidated_Amt_Modified]))

the same for [EXCH_AMT]

Measure = IF(LEFT(VALUES(Transactions[ACCOUNTNUMBER]),1)="1", SELECTEDVALUE([Consolidated_Amt_Modified]), SELECTEDVALUE(Transactions[EXCH_AMT]))

do not hesitate to give a kudo to useful posts and mark solutions as solution


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn
sai1092
Frequent Visitor

Hi @az38 ,

 

Thank you for the reply. When I try to use SELECTEDVALUE or CALCULATE(SUM())), I get the following error:

Parameter is nor the correct type

image.png

 

Hi,

 

How can I solve this? I still am stuck in the issue with creating a calculated column that decides between the two amounts based on the account number. I tried using Custom Columns, but none of them work. 

 

Thank you

Anonymous
Not applicable

Hi @sai1092 

 

Don't use selectevalue before meausure, Use it before column only.

 

Remove first selectedvalue and keep second selectedvalue in  your DAX.

 

 

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

Hi @Anonymous ,

 

The DAX formula worked but I get the following error when I put the measure in a visual. How to solve the issue when the IF statment has to choose where one calculation is being built on a temp table and the other an aggregate?

 

image.png

 

Anonymous
Not applicable

Try this

 

Measure = IF(LEFT(Transactions[ACCOUNTNUMBER],1)="1", [Consolidated_Amt_Modified], SELECTEDVALUE(Transactions[EXCH_AMT]))

 

I assume [Consolidated_Amt_Modified] is measure and Transactions[EXCH_AMT] is column.

 

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.