DAX calculation depending on values in 3 columns

[tgjones43]

Hi all

I need to create a measure in DAX that multiplies the value in [Count] by a fixed number (not shown here) that is dependent on the values in 3 other columns. [Width] is a numeric column, with the condition being either <5 or >5. [Species] and [Age] are text columns that have either a value of "A" or "B". So there are 8 possible comibinations, as shown below.

Width	Species	Age	Count
3	A	A	10
7	A	A	10
3	A	B	10
7	A	B	10
3	B	A	10
7	B	A	10
3	B	B	10
7	B	B	10

I assume I need to use IF and AND statements but having had several attempts I cannot get it to work. Can anyone help please?

[jthomson]

One way would be to make a calculated column that's something like:

ConstantLookupValue = 

var widthcheck = if ([width] <5,0,1)

var agecheck = if([Age]="A",0,2)

var speciescheck = if([Species]="A",0,4)

return widthcheck+agecheck+speciescheck

This'll return a value between 0 and 7, that relates to each row in the table you have - then you can relate it to a static table with numbers 0-7 and the related constant you haven't shown

[tgjones43]

Hi @jthomson That looks good, but I need a modification for my data. The issue is that the [Width] column is from one table and [Age] and [Species] are from a different table and the tables are connected by a One to Many relationship, i.e. for each value of [Width] there are several values of [Age] and [Species].

Is it still possible to do the calculation in this scenario?

Thank you.

[v-lili6-msft]

hi, @tgjones43 

It could be done, Please share a simple sample pbix file and the expected output.

Best Regards,
Lin

[tgjones43]

Hi @v-lili6-msft 

Thank you, please find a simple sample file here (https://www.dropbox.com/s/zan7g3o1p3w6gvq/Fish_calculation_example.pbix?dl=0) and below is the required output. The final 2 columns are what I require. I would like to add the Fixed Factor as a Measure, and it is a number that depends on [Survey width] (>5 or <5), [Observable] (Atlantic salmon or Brown / sea trout) and [FW age] (>0+ or 0+) - there are 8 possible combinations. The score is then a simple equation from Count and Fixed Factor.

In the pbix file I have created the table below in the Report view (minus the final 2 columns). The columns of the visualisation are taken from 3 related tables which are connected by 'One to One' and 'One to Many' relationships.

Hopefully this is enough information, but please let me know if you need any more details.

Many thanks!

Site name	Sampling Date/Time	Survey width (m)	Observable	FW age	Count	Fixed Factor	Score
LON03	22/07/2004 12:00	4.15	Atlantic salmon	>0+	1	0.6643	2
LON03	22/07/2004 12:00	4.15	Brown / sea trout	>0+	22	0.6754	33
LON03	22/07/2004 12:00	4.15	Brown / sea trout	0+	13	0.5932	22
LON03	10/08/2015 12:00	5.2	Atlantic salmon	0+	1	0.5106	2
LON03	10/08/2015 12:00	5.2	Brown / sea trout	>0+	13	0.6272	21
LON03	10/08/2015 12:00	5.2	Brown / sea trout	0+	31	0.5666	55

[v-lili6-msft]

hi, @tgjones43 

What is the logic to calculate Fixed Factor and Score?

If you could add a column in 3 - Observations table by this formula

Measure 2 = var widthcheck = if (SELECTEDVALUE('4 - Observation Attributes'[Survey width (m)]) <5,0,1)

var agecheck = if(SELECTEDVALUE('3 - Observations'[FW age])=">0+",0,2)

var speciescheck = if(SELECTEDVALUE('3 - Observations'[Observable])="Atlantic salmon",0,4)

return speciescheck+widthcheck+agecheck

or use this measure as below:

Measure 2 = var widthcheck = if (SELECTEDVALUE('4 - Observation Attributes'[Survey width (m)]) <5,0,1)

var agecheck = if(SELECTEDVALUE('3 - Observations'[FW age])=">0+",0,2)

var speciescheck = if(SELECTEDVALUE('3 - Observations'[Observable])="Atlantic salmon",0,4)

return speciescheck+widthcheck+agecheck

Best Regards,
Lin

[tgjones43]

Hi @v-lili6-msft

Thank you for your help. The Fixed Factor is just a number that is used by the Score column and it depends on values in 3 columns, so if [Survey width] is less than 5m, [Observable] is Atlantic salmon and the [FW Age] is >0+ then the Fixed Factor is 0.6643 (as shown in the first column). The [Score] is just ([Count]/[P value]+0.5).

I tried your formula as a Measure and a Column but they don't quite do what I was hoping (I have refreshed the pbix file in the above link). As a Column, the formula gives a value of 6 no matter what is in the three columns it is looking at. As a Measure, the formula gives different values for the different conditions, which is great, but it brings in extra blank rows for values that aren't present in the original data. The output is shown below, the rows highlighted in red only appear when adding the Measure to the table. Do you know why this is?

Site name	Sampling Date/Time	Survey area (km2)	Survey width (m)	Observable	FW age	Count	Column	Measure
LON03	22/07/2004 12:00	207.5	4.15	Atlantic salmon	>0+	1	6	0
LON03	22/07/2004 12:00			Atlantic salmon	0+			6
LON03	22/07/2004 12:00	207.5	4.15	Brown / sea trout	>0+	22	6	4
LON03	22/07/2004 12:00	207.5	4.15	Brown / sea trout	0+	13	6	6
LON03	10/08/2015 12:00			Atlantic salmon	>0+			6
LON03	10/08/2015 12:00	235	5.2	Atlantic salmon	0+	1	6	3
LON03	10/08/2015 12:00	235	5.2	Brown / sea trout	>0+	13	6	5
LON03	10/08/2015 12:00	235	5.2	Brown / sea trout	0+	31	6	7

[tex628 · Accepted solution]

Measure =
VAR Width = IF(Selectedvalue([Width])<5;"A";"B")
VAR Species = Selectedvalue([Species])
VAR Age = Selectedvalue([Age])
Return
IF(
Width = "A" &&
Species = "A" &&
Age = "A" ; 1 ;
IF(
Width = "A" &&
Species = "A" &&
Age = "B" ; 2 ;
IF(
Width = "A" &&
Species = "B" &&
Age = "B" ; 3 ;
IF(
Width = "B" &&
Species = "B" &&
Age = "B" ; 4 ;
IF(
Width = "B" &&
Species = "B" &&
Age = "A" ; 5 ;
IF(
Width = "B" &&
Species = "A" &&
Age = "A" ; 6 ;
IF(
Width = "B" &&
Species = "A" &&
Age = "B" ; 7 ;
IF(
Width = "A" &&
Species = "B" &&
Age = "A" ; 8 ; BLANK()
))))))))

Replace the numeric values in red with your fixed values

[tgjones43]

Thanks all for your help.

The solution by @tex628 is the one that worked best for me, although I had to make a small alteration to lines 3 and 4 to include the condition, as below:

Measure =
VAR Width = IF(SELECTEDVALUE([Width])<5,"A","B")
VAR Species = IF(SELECTEDVALUE([Species])="Atlantic salmon","A","B")
VAR Age = IF(SELECTEDVALUE([Age])="0+","A","B")
Return
IF(
Width = "A" &&
Species = "A" &&
Age = "A" , 0.5395 ,
IF(
Width = "A" &&
Species = "A" &&
Age = "B" , 0.6643 ,
IF(
Width = "B" &&
Species = "A" &&
Age = "A" , 0.5106 ,
IF(
Width = "B" &&
Species = "A" &&
Age = "B" , 0.5547 ,
IF(
Width = "A" &&
Species = "B" &&
Age = "A" , 0.5932,
IF(
Width = "A" &&
Species = "B" &&
Age = "B" , 0.6754 ,
IF(
Width = "B" &&
Species = "B" &&
Age = "A" , 0.5666 ,
IF(
Width = "B" &&
Species = "B" &&
Age = "B" , 0.6272 , BLANK()
))))))))

[v-lili6-msft]

hi, @tgjones43 

First, you should know that calculated column and calculate table can't be affected by any slicer. 
Notice:
1. Calculation column/table not support dynamic changed based on filter or slicer.
2. Measure can be affected by filter/slicer, so you can use it to get dynamic summary result.
here is reference:
https://community.powerbi.com/t5/Desktop/Different-between-calculated-column-and-measure-Using-SUM/t...
https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/

Second, for the blank rows, it relates to the row context and filter context in dax calculation.

for [Observable] is in "3 - Observations" table, if calculation will return two results, so this leads to this case.

just set visual level filter of Survey area (km2) or Survey width (m) is not blank.

Best Regards,
Lin