Forum Discussion
Creating Calculated Columns
I have 3 columns in the table. (1st row is the header)
| COLUMN A | COLUMN B | COLUMN C |
| 20 | D | 100 |
| 30 | B | 20 |
| 60 | A | 60 |
| 50 | D | 30 |
| 25 | D | 10 |
| 27 | D | 10 |
I need to create 5 new calculated columns and get results like this (1st row is header)
| A < 30 | D | C < 50 | A < 30 && D && C < 50 | A < 30 || D || C < 50 |
| True | True | True | ||
| True | True | |||
| True | True | True | ||
| True | True | True | True | True |
| True | True | True | True | True |
I am getting a Circular dependency error. Can anyone please help me?
Hi hitesh1607,
>>I am trying to create a Filter for the user where he can sort a report or visual by clicking options like 'Sales less than 40% or Qty change more than 10%'.
Based on your description, I suggest to create a calculated table, and then create a measure for each filter. Since I don't have your sample, I did the following based on my sample.
1.Create a calculated table contains COLUMN A ,B,C as your description in your first post.
Table =
ADDCOLUMNS (
SUMMARIZE ( 'Sales OrderDetails', 'Sales OrderDetails'[productid] ),
"QTY", CALCULATE ( SUM ( 'Sales OrderDetails'[qty] ) ),
"Salesamonut", 'Sales OrderDetails'[Saleamount]
)
2.Create measures for all your filter(To save time I only created three measures):
A<30 = IF( MAX('Table'[QTY]) <1000,1,0)
3.Create a table for slicer on these measures (How to use measures for slicer, please refer tohttps://www.fourmoo.com/2017/11/21/power-bi-using-a-slicer-to-show-different-measures/😞
4.Create a measure for filter on the visual:
Measure =
VAR SELECTEDVALUE =
SELECTEDVALUE ( Table2[Slicer] )
RETURN
SWITCH (
TRUE (),
SELECTEDVALUE = "A<30", [A<30],
SELECTEDVALUE = "D", [D],
SELECTEDVALUE = "C<50", [C<50]
)
5.Add the measure to the visual level filter:
For more details, please refer to the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EU_kkYgRWzVJr3b7nm634WsB0xtNchanjgHNDOtmubxJ-g?e=drSLyC
Best Regards,
Dedmon Dai
13 Replies
Power BI is different to Excel. Generally speaking you should avoid calculated columns. There are exceptions, but mostly they are not the answer. https://exceleratorbi.com.au/calculated-columns-vs-measures-dax/
What are you trying to acheive here? What are you going to do with this new information? If you do need the columns, you can create conditional columns in Power Query.
I am trying to create a Filter for the user where he can sort a report or visual by clicking options like 'Sales less than 40% or Qty change more than 10%'.
I have done that using bookmarks but I was finding a way to do that using calculate columns.I am sorry I might be doing it wrong. I am trying to learn the best way.
OK, so columns are the correct approach for this. But what is the data in each column, and how is it related to the filters you want to apply? What is the data in A and C? What is the data in B?
FrankAT - Hi Frank please check my post reply to Matt. I have explained that Column A and Column B are measures. Sorry for the misunderstanding.
Calculated columns should be avoided if at all possible. Push this back into Power Query. Put this code in a Blank Query to see what I did:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjJQ0lFyAWJDAwOlWJ1oJWOQgBMQG0H4ZiC+IxCbQfimMA3GEL6RKdwACN8ciR8LAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"COLUMN A" = _t, #"COLUMN B" = _t, #"COLUMN C" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"COLUMN A", Int64.Type}, {"COLUMN B", type text}, {"COLUMN C", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "A < 30", each [COLUMN A] < 30, type logical), #"Added Custom1" = Table.AddColumn(#"Added Custom", "D", each [COLUMN B] = "D", type logical), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "C < 50", each [COLUMN C] < 50, type logical), #"Added Custom3" = Table.AddColumn(#"Added Custom2", "A < 30 && D && C < 50", each [#"A < 30"] and [D] and [#"C < 50"], type logical), #"Added Custom4" = Table.AddColumn(#"Added Custom3", "A < 30 || D || C < 50", each [#"A < 30"] or [D] or [#"C < 50"], type logical) in #"Added Custom4"1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press DoneIt looks like this when done:
In general, try to avoid calculated columns. There are times to use them, but it is rare. Getting data out of the source system, creating columns in Power Query, or DAX Measures are usually preferred to calculated columns. See these references:
Calculated Columns vs Measures in DAX
Calculated Columns and Measures in DAX
Storage differences between calculated columns and calculated tables
Creating a Dynamic Date Table in Power Queryedhans - Thanks for explaining this. I should have mentioned the columns in the table Column A and Column B are the measures which were created by me using Variables. I am sorry this is my 1st post. I should have cleared that in the starting only.
Hi hitesh1607,
>>I am trying to create a Filter for the user where he can sort a report or visual by clicking options like 'Sales less than 40% or Qty change more than 10%'.
Based on your description, I suggest to create a calculated table, and then create a measure for each filter. Since I don't have your sample, I did the following based on my sample.
1.Create a calculated table contains COLUMN A ,B,C as your description in your first post.
Table =
ADDCOLUMNS (
SUMMARIZE ( 'Sales OrderDetails', 'Sales OrderDetails'[productid] ),
"QTY", CALCULATE ( SUM ( 'Sales OrderDetails'[qty] ) ),
"Salesamonut", 'Sales OrderDetails'[Saleamount]
)
2.Create measures for all your filter(To save time I only created three measures):
A<30 = IF( MAX('Table'[QTY]) <1000,1,0)
3.Create a table for slicer on these measures (How to use measures for slicer, please refer tohttps://www.fourmoo.com/2017/11/21/power-bi-using-a-slicer-to-show-different-measures/😞
4.Create a measure for filter on the visual:
Measure =
VAR SELECTEDVALUE =
SELECTEDVALUE ( Table2[Slicer] )
RETURN
SWITCH (
TRUE (),
SELECTEDVALUE = "A<30", [A<30],
SELECTEDVALUE = "D", [D],
SELECTEDVALUE = "C<50", [C<50]
)
5.Add the measure to the visual level filter:
For more details, please refer to the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EU_kkYgRWzVJr3b7nm634WsB0xtNchanjgHNDOtmubxJ-g?e=drSLyC
Best Regards,
Dedmon Dai
