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,
I am having an issue with the context transition issue which breaks my measures. I have the following measures and it works fine
but when I use "Year" in the slicer, it breaks. Would highly appreciate correction of the measures and any further guidance. Thanks
Applications = CALCULATE ( DISTINCTCOUNT(Transactions[Transaction ID]) ) ProgPerSchool = CALCULATE ( DISTINCTCOUNT ( 'Dim Programs'[Program Code] ), ALLEXCEPT('Dim Programs', Dim Programs'[School Name]), Dim Programs'[Transaction_Count]>0 ) AppPerSchool = CALCULATE ( [Applications], ALLEXCEPT ('Dim Programs','Dim Programs'[School Name])) AppAverageSchl = DIVIDE([AppPerSchool],[ProgPerSchool],99999) ProgAboveAvg= COUNTX ( FILTER ( SUMMARIZE ( ALL ( 'Dim Programs'[Program Name] ); 'Dim Programs'[Program Name]; "ApplicationCount"; [Applications] ); [ApplicationCount] > [AppAverageSchool] ) ); [ApplicationCount])
Solved! Go to Solution.
Hi @Anonymous ,
I downloaded your file. After my test, I think you should replace "[AppAverageSch]" with "[AppAverageSchl]" in your two measures: "ProgAboveAvg" and "ProgBelowAvg".
ProgBelowAvg = COUNTX ( FILTER ( SUMMARIZE ( 'Dim Programs', 'Dim Programs'[Program ID], 'Dim Programs'[Program Name], "AppCt", COUNT ( Transactions[Transaction ID] ) ), [AppCt] < [AppAverageSchl] ), [AppCt] )
ProgAboveAvg = COUNTX ( FILTER ( SUMMARIZE ( 'Dim Programs', 'Dim Programs'[Program ID], 'Dim Programs'[Program Name], "AppCt", COUNT ( Transactions[Transaction ID] ) ), [AppCt] >= [AppAverageSchl] ), [AppCt] )
Here is the result.
Hi @Anonymous ,
I downloaded your file. After my test, I think you should replace "[AppAverageSch]" with "[AppAverageSchl]" in your two measures: "ProgAboveAvg" and "ProgBelowAvg".
ProgBelowAvg = COUNTX ( FILTER ( SUMMARIZE ( 'Dim Programs', 'Dim Programs'[Program ID], 'Dim Programs'[Program Name], "AppCt", COUNT ( Transactions[Transaction ID] ) ), [AppCt] < [AppAverageSchl] ), [AppCt] )
ProgAboveAvg = COUNTX ( FILTER ( SUMMARIZE ( 'Dim Programs', 'Dim Programs'[Program ID], 'Dim Programs'[Program Name], "AppCt", COUNT ( Transactions[Transaction ID] ) ), [AppCt] >= [AppAverageSchl] ), [AppCt] )
Here is the result.
Hi @Anonymous
Can you share the actual data model? i.e. the tables involved and their relationships... and all the fields/slicers that are being used in the visuals, since they will affect the result. If you can share the pbix (of a simplified version that reproduces the problem ) even better. What do you exactly mean the measure "breaks"? What is the expected result?
Hi @AlB
Please find the pbix file here
The dataset has around 157 distinct programs and only 69 of them are having applications and my distinct count measure calculates only these 69 programs. The school received 792 applications during the entire duration which yields an average of 11.48 application per program. Both the measures as well as when the filter is applied manually on applications for programs with > and < to 11.48 give the correct result of 34 and 35 programs respectively.
In 2015, School received 198 applications for the distinct 69 programs which yield an average of 2.87 application per program. When I apply a filter on programs where applications are greater or equal to 2.87, it filters 69 programs to 25 programs with applications more than average of school application with a sub-total of 118. Similarly, programs with less than 2.87 applications are filtered to 42 programs with application less than average of school application with a sub-total of 80. (118+80 =198). However, the measures give an incorrect result of 63 and 4.
Thanks for your time and support.
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |