DAX or Measures and Columns?

[payyadi]

Hey experts, I'm a newbie to PBI.

I have a table in PBI Desktop which I have imported from Database view.( this view is a result of multipe dimensions and facts join)

Now I have a complex SQL query having multiple aggregations and group by on multiple columns. The resulted attributes help me built my report.

A mocked up query is provided below, I'm looking for help around implementing this, Should I create derivation logic for each column in Power Query by using Calculated columns or Measures? Or can I write one big DAX function(create table using SUMMARIZE/ADD COLUMNS/GROUP BY) which can result in a table with all the required columns and data?

SELECT 

D_date.CalenderDate,

D_Fund.Fund_Cd,

CASE

WHEN D_Currency.Currency_Code = "Dollar" THEN "United States of America"

WHEN D_Currency.Currency_Code = "Pound" THEN "United Kingdom"

WHEN D_Currency.Currency_Code = "Dollar" and D_Currency.Country_Code = "CAN" THEN "Canada"

ELSE "Others" as Actual_Country,

SUM ( D_Currency.Amount) as Total_Amt,

SUM ( D_Fund.Fund_Price) as Fund_Total,

(Total_Amt + Fund_Total) as Gross_Amt

FROM

Source_View_imported

WHERE

D_fund.Fund_System_Cd =1

AND D_Date.Validate = CAST("2020/09/10" as date)

GROUP BY

D_date.CalenderDate,

D_Fund.Fund_Cd,

CASE

WHEN D_Currency.Currency_Code = "Dollar" THEN "United States of America"

WHEN D_Currency.Currency_Code = "Pound" THEN "United Kingdom"

WHEN D_Currency.Currency_Code = "Dollar" and D_Currency.Country_Code = "CAN" THEN "Canada"

ELSE "Others" as Actual_Country

I have 9 such queries to be created with different logics and then I will have to join them, so one step solution/DAX or anything will help.

Thanks a lot for your time in reading this and helping out. Cheers

Ton of thanks to all those who have been helping tremendously and fast pacing our learning experience, special thansk to @Greg_Deckler/@amitchandak your solutions have helped big time for over a month now.

[amitchandak · Accepted solution]

@payyadi , You need to have a new column like

Actual_Country =
Switch ( True() ,
[Currency_Code] = "Dollar" && [Country_Code] = "CAN" , "Canada",
[Currency_Code] = "Dollar" , "United States of America"
[Currency_Code] = "Pound" , "United Kingdom"
, "Others" )

Other like Amount, Fund_Price just take Sum as aggregation in visualization

these two you case use in slicer or measures filter

D_fund.Fund_System_Cd =1
D_Date.Validate

Refer if my Dax vs SQL series can help: https://www.youtube.com/watch?v=WlvQ_SGy4iA&list=PLPaNVDMhUXGZNyKU0PgG2g3P0c6CPjMnj

[payyadi]

Hi Amit, Thanks for your quick response. I'm in for your solution but how should I refer back the same column I just created? Like if you see my sample query I reuse the just created columns,

(Total_Amt + ...) as Gross_Amt

OR

even if I want to reuse Actual_Currency as a condition for deriving a new column?

Thanks in advance