topic Re: Two filters for the same column in DAX Commands and Tips

Two filters for the same column

atziovara — Fri, 01 Dec 2023 14:29:17 GMT

I want to compute the Weighted Average for one of the following Questions, based on the value of the Answer to another Question. For example, I would like to compute the Weighted Average of the Liquidity Problems Intensity for companies who expect that their Demand in the next 6 months will increase (Question= "Expected change in demand in the next 6 months", Answer = 1). My dataset is like this:

Table Name: TableBI

Wave	Company Index	Company ID	Weights	Questions	Answers	Weighted Answers	Periods
1	1	1-1	0,006	Demand change compared to the previous 6 months	-1	-0,006	2022 H2
1	1	1-1	0,006	Expected change in demand in the next 6 months	1	0,006	2023 H1
1	1	1-1	0,006	Liquidity problems intensity	1	0,006	2022 H2
1	1	1-1	0,006	Expected change in employment	0	0	2023 H1
1	2	1-2	0,001	Demand change compared to the previous 6 months	1	0,001	2022 H2
1	2	1-2	0,001	Expected change in demand in the next 6 months	1	0,001	2023 H1
1	2	1-2	0,001	Liquidity problems intensity	-1	-0,001	2022 H2
1	2	1-2	0,001	Expected change in employment	1	0,001	2023 H1
1	3	1-3	0,0057	Demand change compared to the previous 6 months	1	0,0057	2022 H2
1	3	1-3	0,0057	Expected change in demand in the next 6 months	0	0	2023 H1
1	3	1-3	0,0057	Liquidity problems intensity	0	0	2022 H2
1	3	1-3	0,0057	Expected change in employment	0	0	2023 H1
2	1	2-1	0,006	Demand change compared to the previous 6 months	1	0,006	2023 H1
2	1	2-1	0,006	Expected change in demand in the next 6 months	0	0	2023 H2
2	1	2-1	0,006	Liquidity problems intensity	0	0	2023 H1
2	1	2-1	0,006	Expected change in employment	0	0	2023 H2
2	2	2-2	0,001	Demand change compared to the previous 6 months	1	0,001	2023 H1
2	2	2-2	0,001	Expected change in demand in the next 6 months	1	0,001	2023 H2
2	2	2-2	0,001	Liquidity problems intensity	-1	-0,001	2023 H1
2	2	2-2	0,001	Expected change in employment	1	0,001	2023 H2
2	3	2-3	0,0057	Demand change compared to the previous 6 months	1	0,0057	2023 H1
2	3	2-3	0,0057	Expected change in demand in the next 6 months	0	0	2023 H2
2	3	2-3	0,0057	Liquidity problems intensity	-1	-0,0057	2023 H1
2	3	2-3	0,0057	Expected change in employment	1	0,0057	2023 H2

*Weighted Answers is calculated as: Weights * Answers

**In the column "Answers" -1 denotes decrease (or zero problems for Liquidity problems intensity), 0 denotes stability (or low to medium intensity problems), and 1 denotes increase (or high intensity problems).

I created a disconnected table "Selected Characteristics" as:

SelectedCharacteristics =

SUMMARIZE( 'TableBI',

'TableBI'[Questions],

'TableBI'[Answers])

I created the following measure:

Weighted Average =

VAR SelectedQuestion1 = SELECTEDVALUE('TableBI'[Questions])

VAR SelectedQuestion2 = SELECTEDVALUE('SelectedCharacteristics'[Questions])

VAR SelectedPrice = SELECTEDVALUE('SelectedCharacteristics'[Answers])

RETURN

DIVIDE(

SUMX(

FILTER(

'TableBI',

NOT(ISBLANK('TableBI'[Weighted Answers])) &&

NOT(ISBLANK('TableBI'[Weights])) &&

'TableBI'[Questions] = SelectedQuestion1 &&

CALCULATE(

VALUES('TableBI'[Answers]), 'TableBI'[Questions] = SelectedCharacteristic2, 'TableBI'[Τιμή] = SelectedPrice, ALLEXCEPT('TableBI', 'TableBI'[Company Index])

)

), 'Table'[Weighted Answers]

SUMX(

FILTER(

'TableBI',

NOT(ISBLANK('TableBI'[Weighted Answers])) &&

NOT(ISBLANK('TableBI'[Weights])) &&

'TableBI'[Questions] = SelectedCharacteristic1 &&

CALCULATE(

VALUES('TableBI'[Answers]), 'TableBI'[Questions] = SelectedCharacteristic2, 'TableBI'[Answers] = SelectedPrice, ALLEXCEPT('TableBI', 'TableBI'[Company Index])

)

'TableBI'[Weights]

)

I added two slicers: one with Questions from TableBI and one with Questions and Answers from SelectedCharacteristics.
I added a Clustered Column Chart that has Waves on its X-Axis and Weighted Average on its Y-Axis.

The problem that I am encountering is that this formula is only working when selected Answer = 1 or Answer = -1, however, when Answer = 0 I get back no values at all on the graph.

Re: Two filters for the same column

lbendlin — Sun, 03 Dec 2023 01:11:23 GMT

Since you are multiplying by zero that is the correct result of the calculation. What would be your expected outcome? What if you shifted your values by 2? 1 becomes 3, 0 becomes 2, -1 becomes 1 ?

Re: Two filters for the same column

atziovara — Mon, 04 Dec 2023 09:39:04 GMT

lbendlin Thank you for your response!

I don't want the calculation to take place on the row of 'Table'[Weighted Answers] where 'Table'[Questions] =SelectedQuestion2. The row of 'Table'[Weighted Answers] which will be summed nees to be the same as the row where 'Table'[Questions] = SelectedCharacteristic1. Therefore, no multiplication by 0 should take place.

Examples:

(1) SelectedQuestion1 = "Liquidity problems intensity"

SelectedQuestion2 = "Expected change in demand in the next 6 months"

SelectedPrice = 1

The expected result should be: [0,006 + (-0,001) + (-0,001)] / (0,006 + 0,001 + 0,001) = 0,5

(2) SelectedQuestion1 = "Liquidity problems intensity"

SelectedQuestion2 = "Expected change in demand in the next 6 months"

SelectedPrice = 0

The expected result should be: [0 + 0 + (-0,0057)] / (0,0057 +0,006 + 0,0057) = -0,32759

Re: Two filters for the same column

lbendlin — Tue, 05 Dec 2023 01:28:41 GMT

I don't see a "SelectedPrice" column in your sample data?

Re: Two filters for the same column

Anonymous — Tue, 05 Dec 2023 09:55:39 GMT

Hi atziovara ,

Thanks for reaching out to us with your problem. Base on your description, there is one table named "TableBI". And you execute the following steps:

1. Create another disconnected calculated table "SelectedCharacteristics"

2. Create three sliceres, the applied fields on the slicers are from the field [Questions] of table 'TableBI', the field [Questions] of table "SelectedCharacteristics" and the field [Answers] of table "SelectedCharacteristics"

3. Create a measure to get the value --Here I'm not clear about the calculation logic, could you please explain more details on it with the examples and screenshot? Thank you.

For example:

(1) SelectedQuestion1 = "Liquidity problems intensity"

SelectedQuestion2 = "Expected change in demand in the next 6 months"

SelectedPrice = 1

The expected result should be: [0,006 + (-0,001) + (-0,001)] / (0,006 + 0,001 + 0,001) = 0,5

According to the sample data and slicers, it will return the below data.

Why the expected result not be: [0+(-0,001)+(-0,0057)]/[0,006 + 0,001 +0,0057]?

Best Regards

Re: Two filters for the same column

atziovara — Mon, 11 Dec 2023 11:50:29 GMT

lbendlin
As mentioned on my original post - inside the measure Weighted Average:
VAR SelectedPrice = SELECTEDVALUE('SelectedCharacteristics'[Answers])

This means that SelectedPrice is a slicer that takes its values from the column [Answers] of table 'SelectedCharacteristics'.

SelectedCharacteristics is a disconnected table that has been created from summarizing the original table, 'TableBI':

SelectedCharacteristics =

SUMMARIZE( 'TableBI',

'TableBI'[Questions],

'TableBI'[Answers])

Re: Two filters for the same column

atziovara — Mon, 11 Dec 2023 13:43:20 GMT

Hello Anonymous ! Thank you very much for your answer!

Let me analyse the example of the calculation I am trying to perform:

(1) SelectedQuestion1 = "Liquidity problems intensity"

SelectedQuestion2 = "Expected change in demand in the next 6 months"

SelectedPrice = 1

The expected result should be: [0,006 + (-0,001) + (-0,001)] / (0,006 + 0,001 + 0,001) = 0,5

I would like to compute the Weighted Average of Liquidity problems intensity for the companies who have asnwered that the Expected change in demand in the next 6 months will be 1.

The Questions & Answers in RED are the ones who check the conditions of the filtering for each Company ID. They ensure that the weighted average of Liquidity problems intensity for each company and wave will be calculated for SelectedQuestion2 = "Expected change in demand in the next 6 months" & SelectedPrice = 1.

The weighted average is calculated as the sum of the Weighted Answers of Liquidity problems intensity (yellow background) divided by the sum of the Weights of Liquidity problems intensity (green background). It is based on the typical mathematical formula:

Weighted Mean = Σ (wi * xi) / Σwi

Therefore, the expected result is: [0,006 + (-0,001) + (-0,001)] / (0,006 + 0,001 + 0,001) = 0,5

(2) SelectedQuestion1 = "Liquidity problems intensity"

SelectedQuestion2 = "Expected change in demand in the next 6 months"

SelectedPrice = 0

The logic is the same as above.

The expected result is: [0 + 0 + (-0,0057)] / (0,0057 +0,006 + 0,0057) = -0,32759