Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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 , 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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
39 | |
20 | |
19 | |
16 | |
15 |
User | Count |
---|---|
47 | |
28 | |
25 | |
17 | |
17 |