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.
Hello,
I work on a issue that i have partially resolve.
I want to count only 1 Revisions status by ID, The good status is the status of Max date in date slicer.
Actually, the max date is static and not dynamic relativ to date slicer.
I want to my mesure return the status relative to the max date in the date slicer.
Somebody can help to achieve that ?
You can see below my mesure, and my pbix.
Measure = VAR a = CALCULATE ( MAX ( 'Table'[Revision Date] ), ALLEXCEPT ( 'Table', 'Table'[ID] ) ) RETURN CALCULATE ( MAX ( 'Table'[Revision Status] ), ALLEXCEPT ( 'Table', 'Table'[ID] ), FILTER ( ALL ( 'Table' ), 'Table'[Revision Date] = a ) ) Measure 2 = IF ( [Measure] = MAX ( 'Table'[Revision Status] ), 1, 0 ) count_A = VAR a = CALCULATE ( COUNTROWS ( 'Table' ), FILTER ( 'Table', [Measure 2] = 1 && 'Table'[Measure] = "A" ) ) RETURN IF ( ISBLANK ( a ), 0, a ) count_B = VAR b = CALCULATE ( COUNTROWS ( 'Table' ), FILTER ( 'Table', [Measure 2] = 1 && 'Table'[Measure] = "B" ) ) RETURN IF ( ISBLANK ( b ), 0, b ) Count_c = VAR c = CALCULATE ( COUNTROWS ( 'Table' ), FILTER ( 'Table', [Measure 2] = 1 && [Measure] = "C" ) ) RETURN IF ( ISBLANK ( c ), 0, c )
Thanks by advance
Solved! Go to Solution.
hi, @Anonymous
Ok, For "Measure : must show the status of the max date for each ID (this actually dont work)"
You could use the formula as above
Measure = VAR a = CALCULATE ( MAX ( 'Feuil1'[Revision Date] ),FILTER(ALLSELECTED(Feuil1),Feuil1[ID]=MAX(Feuil1[ID]))) RETURn CALCULATE ( MAX ( 'Feuil1'[Revision Status] ), ALLEXCEPT ( 'Feuil1', 'Feuil1'[ID] ), FILTER ( ALL( 'Feuil1'), 'Feuil1'[Revision Date] = a ) )
and for "Count_A/B/C : Count the row for each status who respond to previous test measure. "
I think you need to use DISTINCTCOUNT instead of COUNTROWS in these three measure
count_A = VAR a = CALCULATE ( DISTINCTCOUNT( Feuil1[ID]), FILTER ( Feuil1, [Measure 2] = 1 && [Measure]= "A" ) ) RETURN IF ( ISBLANK ( a ), 0, a )
count_B = VAR b = CALCULATE ( DISTINCTCOUNT ( Feuil1[ID] ), FILTER ( Feuil1 , [Measure 2] = 1 && Feuil1 [Measure] = "B" ) ) RETURN IF ( ISBLANK ( b ), 0, b )
Count_c = VAR c = CALCULATE ( DISTINCTCOUNT ( Feuil1[ID] ), FILTER (Feuil1, [Measure 2] = 1 && [Measure] = "C" ) ) RETURN IF ( ISBLANK ( c ),0, c )
and here is my demo pbix file, please try it.
Best Regards,
Lin
hi, @Anonymous
If you could use this formula to add a measure
Measure = VAR a = CALCULATE ( MAX ( 'Feuil1'[Revision Date] ),FILTER(ALLSELECTED(Feuil1),Feuil1[ID]=MAX(Feuil1[ID]))) RETURn CALCULATE ( MAX ( 'Feuil1'[Revision Status] ), ALLEXCEPT ( 'Feuil1', 'Feuil1'[ID] ), FILTER ( ALL( 'Feuil1'), 'Feuil1'[Revision Date] = a ) )
If not your case, please share your expected output based on this sample report.
Best Regards,
Lin
Hello @v-lili6-msft,
Thanks for your anwer, i have done a screenshot that show the expected result with KPI.
Let me know if you need more explaination.
Thanks by advance 🙂
hi, @Anonymous
When date is from 1/1/2018 to 6/7/2018, Why count_A is 1 not 2?
What is the logic of measure 2?
Measure 2 = IF ( [Measure] = CALCULATE(MAX ( 'Feuil1'[Revision Status] ),ALLSELECTED(Feuil1[Revision Date])), 1, 0 )
could you explain the expected output with the sample data?
Best Regards,
Lin
When date is from 1/1/2018 to 6/7/2018, Why count_A is 1 not 2?
Because the status is the status of max date for each ID is
A1 = A
A2 = C
A3 = C
A4 = B
So there is only 1 ID with A status.
What is the logic of measure 2?
Measure 2 = IF ( [Measure] = CALCULATE(MAX ( 'Feuil1'[Revision Status] ),ALLSELECTED(Feuil1[Revision Date])), 1, 0 )
The logic is :
Measure : must show the status of the max date for each ID (this actually dont work)
Measure 2 : must test if the status of each line = to the status of max date (measure)
Count_A/B/C : Count the row for each status who respond to previous test measure.
The final objective of this sequence, is to build a mesure who reponsd at these criteria :
Actually is work, but the status show by "Measure" isn't relative to date slicer.
could you explain the expected output with the sample data?
I already explain with sample data. Real dataset is more complexe.
But i can give you a real life use case.
"imagine that you have a warranty problem with your phone. You send it to the warranty department and here's what happens internally:
The issue is entered in the system with an ID.
An issue is then treated by different collaborators, each time they go through a step he gets a status.
For example.
Issue: ID1 (broken screen)
- RevisionID 1 : Status = A (to be done)
- RevisionID 2 : Status = B (in progress)
- RevisionID 3 : Status = C (Done)
So, for BI purposes, the service manager needs to know the number of issues "to be done", the number of issues "In progress".....
But cause of revisionIDs have a chronological order, if we use a date slicer, the status is relative to the revision date."
Thanks for your time.
I hope to help with my explanations
hi, @Anonymous
Ok, For "Measure : must show the status of the max date for each ID (this actually dont work)"
You could use the formula as above
Measure = VAR a = CALCULATE ( MAX ( 'Feuil1'[Revision Date] ),FILTER(ALLSELECTED(Feuil1),Feuil1[ID]=MAX(Feuil1[ID]))) RETURn CALCULATE ( MAX ( 'Feuil1'[Revision Status] ), ALLEXCEPT ( 'Feuil1', 'Feuil1'[ID] ), FILTER ( ALL( 'Feuil1'), 'Feuil1'[Revision Date] = a ) )
and for "Count_A/B/C : Count the row for each status who respond to previous test measure. "
I think you need to use DISTINCTCOUNT instead of COUNTROWS in these three measure
count_A = VAR a = CALCULATE ( DISTINCTCOUNT( Feuil1[ID]), FILTER ( Feuil1, [Measure 2] = 1 && [Measure]= "A" ) ) RETURN IF ( ISBLANK ( a ), 0, a )
count_B = VAR b = CALCULATE ( DISTINCTCOUNT ( Feuil1[ID] ), FILTER ( Feuil1 , [Measure 2] = 1 && Feuil1 [Measure] = "B" ) ) RETURN IF ( ISBLANK ( b ), 0, b )
Count_c = VAR c = CALCULATE ( DISTINCTCOUNT ( Feuil1[ID] ), FILTER (Feuil1, [Measure 2] = 1 && [Measure] = "C" ) ) RETURN IF ( ISBLANK ( c ),0, c )
and here is my demo pbix file, please try it.
Best Regards,
Lin
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 |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |