if statement

[BeWu]

Hello PowerBi fans !!!

I have a question, probably easy for some of you. Is it possible to create a measure that take a table for calculation depending on a value in the other table ? I mean,  if a value in a column A = 1, calculate something from table_A and if the value is A=2 calculate something from table_B. I have googled, that it could be possible, but before I start experimenting with power query and tables I wanted to ask experts. Thanks a lot. 

[edhans · Accepted solution]

You are mixing terms here. Measures are only in DAX and don't work in Power Query. But you can do what you want in either environment.

In Power Query, you could do something like this:

if Table.RowCount(SomeTable) > 100 then Table1 else Table2

So if SomeTable has more than 100 records, it will return the contents of Table1, othewise Table2. Then your next step would be to act on that data.

In DAX...

VAR varLargeData = SUMX(Table1, Table1[Quantity] * Table1[Price])
VAR varSmallData = SUMX(Table2, Table2[Quantity] * Table2[Price])
VAR Result = 
IF(
   COUNTROWS(SomeTable) > 100,
   varLargeData,
   varSmallData
)
RETURN
   Result

DAX will only calculate the variable it needs. If SomeTable has 59 rows for example, then it will not bother calculating varLargeData.

If you need more help though, provide some real info we can work with, including sample files.
How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

[Anonymous · Accepted solution]

Hi @BeWu ,

You can use DAX to complete this operation. Below I will give a simple example.

Sample data:

If column A is equal to 1, calculate the value when A=1 in table_A, if column A is equal to 2, calculate the sum of all values in table B.

Measure = IF(MAX('table_A'[A])=1,SUM(table_A[Value]),IF(MAX('table_A'[A])=2,SUMX(ALL(table_B),[Value])))

In addition to using the IF function, you can also use the SWITCH function.

Measure2 = SWITCH(MAX('table_A'[A]),1,SUM(table_A[Value]),2,SUMX('table_B',[Value]))

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[edhans · Accepted solution]

You are mixing terms here. Measures are only in DAX and don't work in Power Query. But you can do what you want in either environment.

In Power Query, you could do something like this:

if Table.RowCount(SomeTable) > 100 then Table1 else Table2

So if SomeTable has more than 100 records, it will return the contents of Table1, othewise Table2. Then your next step would be to act on that data.

In DAX...

VAR varLargeData = SUMX(Table1, Table1[Quantity] * Table1[Price])
VAR varSmallData = SUMX(Table2, Table2[Quantity] * Table2[Price])
VAR Result = 
IF(
   COUNTROWS(SomeTable) > 100,
   varLargeData,
   varSmallData
)
RETURN
   Result

DAX will only calculate the variable it needs. If SomeTable has 59 rows for example, then it will not bother calculating varLargeData.

If you need more help though, provide some real info we can work with, including sample files.
How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.