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.
Thanks in advance for any assistance you may provide.
I have a matrix in which I would like to calculate the difference (Delta) between rows in the matrix, by column.
Data
As Of Date Error Type Date Index 07/30/2019 Error Type 1 1 07/30/2019 Error Type 1 1 07/30/2019 Error Type 1 1 07/30/2019 Error Type 2 1 07/30/2019 Error Type 2 1 07/31/2019 Error Type 1 2 07/31/2019 Error Type 1 2 07/31/2019 Error Type 1 2 07/31/2019 Error Type 1 2 07/31/2019 Error Type 1 2 07/31/2019 Error Type 2 2 07/31/2019 Error Type 2 2 07/31/2019 Error Type 2 2 07/31/2019 Error Type 2 2 07/31/2019 Error Type 2 2 07/31/2019 Error Type 2 2 07/31/2019 Error Type 2 2 07/31/2019 Error Type 2 2 07/31/2019 Error Type 2 2 08/01/2019 Error Type 1 3 08/01/2019 Error Type 1 3 08/01/2019 Error Type 1 3 08/01/2019 Error Type 1 3 08/01/2019 Error Type 1 3 08/01/2019 Error Type 1 3 08/01/2019 Error Type 2 3 08/05/2019 Error Type 1 4 08/05/2019 Error Type 1 4 08/05/2019 Error Type 2 4 08/05/2019 Error Type 2 4 08/05/2019 Error Type 2 4 08/05/2019 Error Type 2 4 08/05/2019 Error Type 2 4 08/05/2019 Error Type 2 4
The data has several "Error Types" for each "As Of Date".
I created the Date Index column on the table for reference purposes in the Delta measure.
Date Index = RANKX('Data Errors' , 'Data Errors'[As Of Date] ,, ASC , Dense)
I created a Delta measure, but I don't know how to dynamically reference each Error Type. Note the hard refrence to "Error Type 1" (highlighted in red).
Delta = VAR PRIOR_COUNT = VAR CURRENT_RANK = SELECTEDVALUE('Data Errors'[Date Index]) RETURN CALCULATE(COUNTROWS('Data Errors'), FILTER( FILTER(ALL('Data Errors'),'Data Errors'[Date Index]=CURRENT_RANK-1), 'Data Errors'[Error Type]="Error Type 1")) RETURN COUNTROWS('Data Errors') - PRIOR_COUNT
Matrix (The second matrix to the right is to show the totals without a total for the Delta column.)
The Delta column is correct for only the Error Type 1 column, because that is hardcoded into the measure.
Can, and will, someone please help me dynamically reference the Error Type in my measure so I can have a delta column for each Error Type in my table?
Solved! Go to Solution.
Hi @JimKingPowerBI ,
To what I can understand from you question you want to have the result of the difference from one day to the other, believe that you don't need to have a rankx columns, try the following code:
Delta without RANK = VAR PRIOR_COUNT = CALCULATE ( COUNTROWS ( 'Data Errors' ); FILTER ( ALL ( 'Data Errors'[As Of Date] ); 'Data Errors'[As Of Date] = MAXX ( FILTER ( ALL ( 'Data Errors'[As Of Date] ); 'Data Errors'[As Of Date] < MAX ( 'Data Errors'[As Of Date] ) ); 'Data Errors'[As Of Date] ) ) ) RETURN COUNTROWS ( 'Data Errors' ) - PRIOR_COUNT
When you add the error type on the columns this will give context to your measure.
Using the measure you have you need to redo it to:
Delta = VAR PRIOR_COUNT = VAR CURRENT_RANK = SELECTEDVALUE('Data Errors'[Date Index]) RETURN CALCULATE(COUNTROWS('Data Errors'); FILTER( FILTER(ALL('Data Errors');'Data Errors'[Date Index] = CURRENT_RANK-1); 'Data Errors'[Error Type]=SELECTEDVALUE('Data Errors'[Error Type]))) RETURN COUNTROWS('Data Errors') - PRIOR_COUNT
The highleted part is the one that you had hardcoded.
As you can see both results are the same.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @JimKingPowerBI ,
To what I can understand from you question you want to have the result of the difference from one day to the other, believe that you don't need to have a rankx columns, try the following code:
Delta without RANK = VAR PRIOR_COUNT = CALCULATE ( COUNTROWS ( 'Data Errors' ); FILTER ( ALL ( 'Data Errors'[As Of Date] ); 'Data Errors'[As Of Date] = MAXX ( FILTER ( ALL ( 'Data Errors'[As Of Date] ); 'Data Errors'[As Of Date] < MAX ( 'Data Errors'[As Of Date] ) ); 'Data Errors'[As Of Date] ) ) ) RETURN COUNTROWS ( 'Data Errors' ) - PRIOR_COUNT
When you add the error type on the columns this will give context to your measure.
Using the measure you have you need to redo it to:
Delta = VAR PRIOR_COUNT = VAR CURRENT_RANK = SELECTEDVALUE('Data Errors'[Date Index]) RETURN CALCULATE(COUNTROWS('Data Errors'); FILTER( FILTER(ALL('Data Errors');'Data Errors'[Date Index] = CURRENT_RANK-1); 'Data Errors'[Error Type]=SELECTEDVALUE('Data Errors'[Error Type]))) RETURN COUNTROWS('Data Errors') - PRIOR_COUNT
The highleted part is the one that you had hardcoded.
As you can see both results are the same.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @MFelix ,
Thanks so much. You're brilliant!! It worked perfectly... once I replaced your semicolons with commas.
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 |