topic DAX or Measures and Columns? in DAX Commands and Tips

DAX or Measures and Columns?

payyadi — Wed, 23 Sep 2020 03:02:40 GMT

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.

Re: DAX or Measures and Columns?

amitchandak — Wed, 23 Sep 2020 03:30:49 GMT

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

Re: DAX or Measures and Columns?

payyadi — Wed, 23 Sep 2020 13:32:57 GMT

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

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

Thanks in advance