Help translating a microsoft DAX statement to Qlik expression

bartsimpson77 — Wed, 14 Nov 2018 00:50:11 GMT

Hi All,

I have this expression in a microsoft power bi workbook:

=SUM(NUMERATOR_TABLE[NUMERATOR_AMOUNT])
 /
SUMX( RELATEDTABLE(DENOMINATOR_TABLE), DENOMINATOR_TABLE[DENOMINATOR_AMOUNT])

I'm looking for help translating this into a qlik expression.

Basically it's a calculated measure where filters from the numerator table apply only to the numerator of the ratio. Filters in the denominator table's attributes apply to both the numerator and denominator.

The setup is that I have two tables.

One containing people with attributes like state, county, etc.

And one containing expenses generated by those people, with each person able to have none or many different expenses.

The tables are related on the person ID.

The expression is intended to calculate expense totals per person.

It lets me me filter different person categories and different expense categories while always having the denominator of the expression reflect the people meeting the filter selected from attributes in the person table.

The numerator should then be all expenses for the filtered people where the expenses meet the criteria for any filters selected from attributes in the expenses table.

For example, I could filter on the person table attribute state to "Texas" the ratio would be total expenses per person for persons residing in Texas.

If I then filter to "Housing" from the expense category attribute in the expenses table, I would see housing expenses per person, for all people residing in Texas. The denominator here would include counting people who have NO housing expenses.

If I just used sums and didn't have the the sumx and "related table" construct from power BI, these two filters would leave me showing sum of housing expenses for people in TX divided by the count of people residing in TX who had housing expenses instead of dividing by the count of all people in TX with or without housing expenses.

Re: Help translating a microsoft DAX statement to Qlik expression

Anil_Babu_Samineni — Wed, 14 Nov 2018 03:35:53 GMT

As Such Power BI need to assign table names where Qlik Don't need to use Table names. In Qlik, You can define like

=SUM(NUMERATOR_AMOUNT)/RangeSum(Measure1, Measure2)

Re: Help translating a microsoft DAX statement to Qlik expression

bartsimpson77 — Wed, 14 Nov 2018 12:38:11 GMT

Thanks for the reply but I don’t think it’s working. Rangesum seems to add two things together not great the changes filtering behavior I’m after.

Re: Help translating a microsoft DAX statement to Qlik expression

Anil_Babu_Samineni — Wed, 14 Nov 2018 13:54:01 GMT

What is the power BI expression do?

Re: Help translating a microsoft DAX statement to Qlik expression

bartsimpson77 — Wed, 14 Nov 2018 14:13:31 GMT

I described it in the text below it.

topic Help translating a microsoft DAX statement to Qlik expression in App Development

Help translating a microsoft DAX statement to Qlik expression

Re: Help translating a microsoft DAX statement to Qlik expression

Re: Help translating a microsoft DAX statement to Qlik expression

Re: Help translating a microsoft DAX statement to Qlik expression

Re: Help translating a microsoft DAX statement to Qlik expression