Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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 )
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 )
Cheers, thats great, did the trick and took a 10th of the effort & time !
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
25 | |
20 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |