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 have a problem with the matrix filter issue. Now I have a matrix table looks like this:
ItemNo\Margin\Date | week 1 | week2 |
1 | 1 | 1 |
2 | 1 | 0.5 |
3 | 1 | 1.5 |
the original data table looks like this:
Item No | Date | Margin |
1 | week1 | 1 |
1 | week2 | 1 |
2 | week1 | 1 |
2 | week2 | 0.5 |
3 | week1 | 1 |
3 | week2 | 1.5 |
I want to create a filter for this matrix to show items with a margin increase/decrease. (include the former week history)
the ideal result would look like this
margin increase:
Item No\Margin\Date | week1 | week2 |
3 | 1 | 1.5 |
margin decrease:
Item No\Margin\Date | week1 | week2 |
2 | 1 | 0.5 |
Thanks for any help.
Solved! Go to Solution.
Hi @Anonymous,
I suggest you use the following dax to create a calculated column to rank the temNo:
rank =
CALCULATE (
COUNTROWS ( 'Table' ),
FILTER (
ALL ( 'Table' ),
'Table'[tem No] = EARLIER ( 'Table'[tem No] )
&& 'Table'[Index] <= EARLIER ( 'Table'[Index] )
)
)
Then use the following dax to created the filter column:
Column 2 =
VAR b = 'Table'[Margin]
VAR a =
CALCULATE (
MAX ( 'Table'[Margin] ),
FILTER (
ALL ( 'Table' ),
'Table'[tem No] = EARLIER ( 'Table'[tem No] )
&& 'Table'[rank]
= EARLIER ( 'Table'[rank] ) - 1
)
)
VAR c =
CALCULATE (
MAX ( 'Table'[Margin] ),
FILTER (
ALL ( 'Table' ),
'Table'[tem No] = EARLIER ( 'Table'[tem No] )
&& 'Table'[rank]
= EARLIER ( 'Table'[rank] ) + 1
)
)
RETURN
IF (
'Table'[rank] = 1,
SWITCH ( TRUE (), b > c, "decrease", b < c, "increase", BLANK () ),
SWITCH ( TRUE (), b < a, "decrease", b > a, "increase", BLANK () )
)
For more details, please refer to the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EU2VJv_n_n9PpDgia5AoYmYBcJOEMmtSudf5fMKybWzpgQ?e=CVHJel
Best Regards,
Dedmon Dai
Hi @Anonymous ,
It can't be achieved in matrix. Would you please try to pivot column on your table and display values in table visual and then you can filter it by increasing or decreasing:
Then create measure for filtering it by increasing or decreasing:
Measure = SWITCH(TRUE(),MAX('Table'[week2])>MAX('Table'[week1]),0,MAX('Table'[week2])<MAX('Table'[week1]),1,2)
Best Regards,
Dedmon Dai
Hi Dedmon,
Thanks for your reply. I'm afraid this would not be the solution for me. Since I have a relatively large data set here (10 files in a folder and each has ~4000 rows), it is risky to transpose. Still, in the sample data, I showed only 2 weeks' history which is easy to use the switch function with hardcoded week number column, while in reality, I need to keep the flexibility comparing multiple (not a specific number) weeks.
As for now, I successfully use a DAX column to compare the margin increase/decrease, refers to this post (https://community.powerbi.com/t5/Desktop/Conditional-formatting-on-Rows-based-on-previous-value/m-p/...).
What I can see for the next steps are:
-after comparing and marked margin increase/decrease, create a column with DAX -- if the item was marked with increase/decrease for week2, then we can mark week 1 as increase/decrease for the same item number.
-In this case, I can apply this column to a filter to successfully filter the matrix.
the ideal back table would look like this:
Item No | Date | Margin | margin comparison (success with the linked post) | Filter Column |
1 | week1 | 1 | ||
1 | week2 | 1 | ||
2 | week1 | 1 | decrease | |
2 | week2 | 0.5 | decrease | decrease |
3 | week1 | 1 | increase | |
3 | week2 | 1.5 | increase | increase |
Is there any way we can do so?
Hi @Anonymous,
I suggest you use the following dax to create a calculated column to rank the temNo:
rank =
CALCULATE (
COUNTROWS ( 'Table' ),
FILTER (
ALL ( 'Table' ),
'Table'[tem No] = EARLIER ( 'Table'[tem No] )
&& 'Table'[Index] <= EARLIER ( 'Table'[Index] )
)
)
Then use the following dax to created the filter column:
Column 2 =
VAR b = 'Table'[Margin]
VAR a =
CALCULATE (
MAX ( 'Table'[Margin] ),
FILTER (
ALL ( 'Table' ),
'Table'[tem No] = EARLIER ( 'Table'[tem No] )
&& 'Table'[rank]
= EARLIER ( 'Table'[rank] ) - 1
)
)
VAR c =
CALCULATE (
MAX ( 'Table'[Margin] ),
FILTER (
ALL ( 'Table' ),
'Table'[tem No] = EARLIER ( 'Table'[tem No] )
&& 'Table'[rank]
= EARLIER ( 'Table'[rank] ) + 1
)
)
RETURN
IF (
'Table'[rank] = 1,
SWITCH ( TRUE (), b > c, "decrease", b < c, "increase", BLANK () ),
SWITCH ( TRUE (), b < a, "decrease", b > a, "increase", BLANK () )
)
For more details, please refer to the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EU2VJv_n_n9PpDgia5AoYmYBcJOEMmtSudf5fMKybWzpgQ?e=CVHJel
Best Regards,
Dedmon Dai
Hi there,
I just tried to practice this solution to my situation and it didn't work.
I believe it didn't make sense for the EARLIER() usage. Since the item number is not ascending or descending in my situation.
Also, as I mentioned in the former post. I applied the attached method to create a column to show an increase or decrease.
Could you help me find a solution based on my description before?
For instance, I only want to get an idea of how to transfer the "increase" or "decrease" message to the same item in the previous week.
Thanks.
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 |
---|---|
107 | |
98 | |
78 | |
65 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |