topic Re: Convert Tableau calculated filed into Power BI in DAX Commands and Tips

Convert Tableau calculated filed into Power BI

Anonymous — Wed, 08 May 2024 13:43:12 GMT

Hi,

I am in the process of converting a load of Tableau dashboards into Power BI but cannot figure out how to translate this particular measure and was hoping someone could point me in the right direction:

Measure from Tableau:

((RUNNING_SUM(SUM(IF [age]=9 OR [age]=10 THEN [no_of_records] END))-
IFNULL(RUNNING_SUM(SUM(IF [age]=0 OR [age]=1 OR [age]=2 OR [age]=3 OR [age]=4 OR [age]=5 OR [age]=6 THEN [no_of_records] END)),0))/
RUNNING_SUM(SUM([no_of_records]))*100)

age is an int field, contains values from 0 - 10. no_of_records also an int field

Tried converting this into Power BI as:

test =

Var Result1=

CALCULATE(CALCULATE( SUM(Query1[no_of_records]),FILTER(Query1,(Query1[age]) = 9 || Query1[age] = 10)), FILTER( Query1, Query1[Start of Week] <= MAX(Query1[Start of Week])))

Var Result2=

CALCULATE(CALCULATE( SUM(Query1[no_of_records]),FILTER(Query1,(Query1[age]) = 0 || Query1[age]) = 1 || Query1[age]) = 2 || Query1[age]) = 3 || Query1[age]) = 4 || Query1[age]) = 5 || Query1[age]) = 6)),

FILTER( Query1, Query1[Start of Week] <= MAX(Query1[Start of Week])))

var Total = CALCULATE(SUM(Query1[no_of_records]),

FILTER( Query1, Query1[Start of Week] <= MAX(Query1[Start of Week])))

var Result3 = Result1 - Result2

var main = DIVIDE(Result3 , Total) * 100

Return

main

Any help would be appreciated

Many Thanks in advance

Re: Convert Tableau calculated filed into Power BI

lbendlin — Wed, 08 May 2024 20:34:01 GMT

Read about the IN operator.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: Convert Tableau calculated filed into Power BI

Anonymous — Thu, 09 May 2024 09:39:52 GMT

lbendlin Thanks for your contribution on this thread.

Hi Anonymous ,

Please update the formula of measure [test] as below and check if it can return your expected result...

test = VAR _week = SELECTEDVALUE ( Query1[Start of Week] ) VAR Result1 = CALCULATE ( SUM ( Query1[no_of_records] ), FILTER ( ALLSELECTED ( Query1 ), Query1[age] IN { 9, 10 } && Query1[Start of Week] <= _week ) ) VAR Result2 = CALCULATE ( SUM ( Query1[no_of_records] ), FILTER ( ALLSELECTED ( Query1 ), Query1[age] IN { 0, 1, 2, 3, 4, 5, 6 } && Query1[Start of Week] <= _week ) ) VAR Total = CALCULATE ( SUM ( Query1[no_of_records] ), FILTER ( ALLSELECTED ( Query1 ), Query1[Start of Week] <= _week ) ) VAR Result3 = Result1 - Result2 VAR main = DIVIDE ( Result3, Total ) * 100 RETURN main

If no, please provide the required info just as suggested by lbendlin. It would be helpful to get the solution. Thank you.

Best Regards

Re: Convert Tableau calculated filed into Power BI

Anonymous — Fri, 10 May 2024 05:31:19 GMT

Hi Guys,

Thanks for all your efforts and help.

I have achieved desired result with the help of window() function.

test =
var result1 =
CALCULATE(
(CALCULATE(SUM('Query1'[no_of_records]),('Query1'[age]= 0 || 'Query1'[age]= 1 || 'Query1'[age]= 2 || 'Query1'[age]= 3 || 'Query1'[age]= 4 || 'Query1'[age]= 5 || 'Query1'[age]= 6 ))),
WINDOW(1,ABS,
0,REL,
SUMMARIZE(ALLSELECTED(Query1),Query1[Start of Week])))

var result2 =
CALCULATE(
(CALCULATE(SUM('Query1'[no_of_records]),('Query1'[age]= 0 || 'Query1'[age]= 1 || 'Query1'[age]= 2 || 'Query1'[age]= 3 || 'Query1'[age]= 4 || 'Query1'[age]= 5 || 'Query1'[age]= 6 ))),
WINDOW(1,ABS,
0,REL,
SUMMARIZE(ALLSELECTED(Query1),Query1[Start of Week])))

var result3 = CALCULATE(
SUM(Query1[no_of_records]),
WINDOW(1,ABS,
0,REL,
SUMMARIZE(ALLSELECTED(Query1),Query1[Start of Week])))

var result4 = ROUND(((result1 - result2) / result3 * 100),0)

return
result4

Thanks,
pm_97