cancel
Showing results for
Did you mean:
Frequent Visitor

## Tidying up Dax Measures in a single measure

I am currently trying to report on some data from a database and can get the results I need but in a piecemeal way using multiple Measures.

I would like to know if there is an easier way to present this in a single measure rather using multiple measures which is slowing my system down.

As an example if I want to look at some columns in a table by a (Name) (Days Present), (Hair Colour) (Height) and produce a score of 8 if the criteria I want is met and a score of 0 if it isnt I end up writing 3 measures to do this.

The Table is called PupilData

Assume the criteria is (Days Present)>=7 and <=10, (Hair Colour)= "Brown", (Height)=160  - Score 8 if Not Score 0

I split this down into chunks of data to get the end result so an example would be to write a measure

Measure 1 - Dayspresent between 7&10 TF = SELECTEDVALUE(Days Present)>=7 && SELECTEDVALUE(Days Present) <=10

This would then return a true or false so to convert it to a 1 or 0 then

Measure 2 - Dayspresent between 7 & 10 INT = INT([Dayspresent between 7&10 TF])

That would return a 1 or 0 for days beetween 7 and 10 range

Then to produce a result of 8 or 0

Measure 3 - Dayspresent Value 8 = [Dayspresent between 7 & 10 INT]*8

If I then wanted the same for (Hair Colour) & (Height) I would create the 3 measures for each and then use mathamatics to add together and get a result, the process being as follows;

1 - Determine the value if true or false

2 - Convert that true or false to an integer to get 1 or 0

3 - Multiply that integer by the score I required. (in this case to get 8 or 0)

Very long winded but it gets to the requred result by thinking primative binary

a one step solution to get the result of 8 or 0 would cut my measures down by a third if anyone could kindly assist.

Cheers

Dax Novice

1 ACCEPTED SOLUTION
Super User

You can write the DAX more similar to your written criteria than going through all of these steps.

Score =
VAR Days = SELECTEDVALUE ( PupilData[Days Present] )
VAR Hair = SELECTEDVALUE ( PupilData[Hair Color] )
VAR Hgt = SELECTEDVALUE ( PupilData[Height] )
RETURN
IF ( Days >= 7 && Days <= 10 && Hair = "Brown" && Hgt = 160, 8, 0 )
2 REPLIES 2
Super User

You can write the DAX more similar to your written criteria than going through all of these steps.

Score =
VAR Days = SELECTEDVALUE ( PupilData[Days Present] )
VAR Hair = SELECTEDVALUE ( PupilData[Hair Color] )
VAR Hgt = SELECTEDVALUE ( PupilData[Height] )
RETURN
IF ( Days >= 7 && Days <= 10 && Hair = "Brown" && Hgt = 160, 8, 0 )
Frequent Visitor

Cheers, thats great, did the trick and took a 10th of the effort & time !

Announcements

#### The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

#### Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!