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.
Hi All,
I wanted to find previous record in matrix visual based on hierachy displayed below.
Any Help with DAX please help me
Hey,
I couldn't resist and created sample data by myself, here you will find a pbix file. The sheet "Not yet validated" is recreating your requirement. The table "GroupOfStates" represents your data that you use in your post. I also created a table "Sequence", this table represents the sequence of categorical Events (meaning a column that is not represented by a numerical or date data type).
This measure in the table "GroupOfStates" calculates the Previous Value:
PreviousStateValue = SUMX('GroupOfStates' ,var currentGroup ='GroupOfStates'[Group] var currentState = 'GroupOfStates'[State] var previousState = CALCULATE(FIRSTNONBLANK('Sequence'[Predecessor],1) ,'Sequence'[State] = currentState ) return CALCULATE( SUM('GroupOfStates'[Amount]) ,FILTER(ALL('GroupOfStates') ,'GroupOfStates'[Group] = currentGroup && 'GroupOfStates'[State] = previousState ) ) )
This screenshot looks quite similar to your requirement:
Hopefully this is what you are looking for.
Regards
Tom
Hey,
thanks for providing sampledata, here you will find my solution a sample pbix file.
I extracted a sequence number from the column Visitname using the Extraxt Text Function "Text Between Delimiters", see the Applied Steps in the Query Editor:
This is necessary due to the fact that some kind of ordering (a sequence) is mandatory to determine a previous value.
Then i used a DAX statement to create a calculated column:
Previous SiteRateScore = var curSubjectNumber = 'score'[SubjectNumber] var curSequence = 'score'[Sequence] var prevSequence = CALCULATE( MAX('score'[Sequence]) ,FILTER(ALL(score) ,'score'[SubjectNumber] = curSubjectNumber && 'score'[Sequence] < curSequence ) ) return LOOKUPVALUE('score'[SiteRaterScore],score[SubjectNumber],curSubjectNumber,score[Sequence],prevSequence)
Here is a screenshot:
If this solution doesn't meet your requirement, please explain why, this will help me and others to provide another solution and also explain how a sequence within each SubjectNumber can be derived from the data you provided.
Regards
Tom
If this solves your question, please mark this post as answer, and also consider to give Kudo to this post, at least for honoring the time others spent trying to provide an answer.
Hi Yes that is what i wanted but im getting below error in calculation
Previous SiteRateScore 1 = var curSubjectNumber = 'question 1'[SubjectNumber] var curSequence = 'question 1'[Visit Sequence] var prevSequence = CALCULATE( MAX('question 1'[Visit Sequence]) ,FILTER(ALL('question 1') ,'question 1'[SubjectNumber] = curSubjectNumber && 'question 1'[Visit Sequence] < curSequence ) ) return //prevSequence LOOKUPVALUE('question 1'[SiteRaterScore],'question 1'[SubjectNumber],curSubjectNumber,'question 1'[Visit Sequence],prevSequence)
The error indicates, that LOOKUPVALUE would retreive more than one value, this is not possible, so for this reason an Aggregate function has to been applied:
Previous SiteRateScore 1 = var curSubjectNumber = 'question 1'[SubjectNumber] var curSequence = 'question 1'[Visit Sequence] var prevSequence = CALCULATE( MAX('question 1'[Visit Sequence]) ,FILTER(ALL('question 1') ,'question 1'[SubjectNumber] = curSubjectNumber && 'question 1'[Visit Sequence] < curSequence ) ) return //prevSequence //LOOKUPVALUE('question 1'[SiteRaterScore],'question 1'[SubjectNumber],curSubjectNumber,'question 1'[Visit Sequence],prevSequence) CALCULATE( AVERAGE('question 1'[SiteRaterScore]) ,FILTER(ALL('question 1') ,'question 1'[SubjectNumber] = curSubjectNumber && 'question 1'[Visit Sequence] = prevSequence ) )
@TomMartens Got it Worked but still it is not showing correct previous values please check attached Pbix
Hi @chotu27,
Maybe there is a solution you can give it a try. Please check it out in the attachment.
1. In the Query Editor, sort the "SubjectNumber" first, then sort the column "VisitName".
2. Add an index to keep the order we make in step 1.
3. Add a custom column.
if [Index.1] = 0 then [Index.1] else if [VisitName] = #"Added Index"{[Index.1] - 1}[VisitName] then null else [Index.1]
4. Right-click the column "Custom", choose Fill -> Down.
5. Apply the changes, and create a measure.
Measure = VAR lastIndex = CALCULATE ( MAX ( 'Table'[Custom] ), FILTER ( ALLEXCEPT ( 'Table', 'Table'[SubjectNumber] ), 'Table'[Custom] < MAX ( 'Table'[Custom] ) ) ) RETURN IF ( ISBLANK ( lastIndex ) = FALSE () && HASONEVALUE ( 'Table'[VisitName] ), CALCULATE ( SUM ( 'Table'[SiteRaterScore] ), FILTER ( ALL ( 'Table' ), 'Table'[Custom] = lastIndex ) ), 0 )
Best Regards,
Dale
Hi @chotu27,
Is my solution helpful?
Best Regards,
Dale
Hey,
it's quite difficult to provide another solution. My example works with the data you provided, please explain what you expect, and show all the data.
You have to be aware that data will be aggregated,, this is the reason why I choose the aggregate function AVERAGE, but maybe you have to choose SUM.
Regards
Tom
I am not asking about comparison . I asking how to find previous record column to do comparison
Hey,
can you please provide some sample data, that we can easily copy to Power BI and also provide how var1 to varn is sorted, are there possiblities that there are var1 and var3 in a group and for this group var1 provides the previous data for var3?
Regards
Tom
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 |
---|---|
114 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |