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 want to apply a measure filter to a matrix visual after the calculation has taken place instead of before.
In the screenshot below I only want to see the information for Part number DEF.
Current:
Desired:
Because of the extra details at the row level, shown below -
- applying a simple '<0' filter will not give result I'm looking for, because it will filter for <0 at the lowest level of the row hierarchy and then calculate, instead of filtering only after the calculation is complete.
A note, just in case it matters: when the matrix is collapsed, it displays the evaluation for "PJQOH", which is a running total of the rows above PJQOH (PJQOH means 'projected quantity on hand'). This custom display is achieved using IF( HASONEVALUE ( <column> ) ).
I hope this is enough information. Other answers have made me understand what is going wrong, but I've not been able to apply their solutions to this situation.
Link to file: https://drive.google.com/file/d/1JzT1Cwqh5anJ_A6dvVuWdGGpz73qj3Mt/view?usp=sharing
Solved! Go to Solution.
Hi @DCELL ,
You can try the following formula:
Filter_Consolidate =
VAR temp_table =
CALCULATETABLE (
FILTER (
ADDCOLUMNS (
SUMMARIZE ( DemandSupply, DemandSupply[Part], Cal[Fiscal Year], Cal[rDate] ),
"_Consolidated", [Consolidated Planning Data]
),
[_Consolidated] < 0
),
ALL ( Cal ),
ALLEXCEPT ( DemandSupply, DemandSupply[Part] )
)
RETURN
COUNTROWS ( temp_table )
Has you can see below is only presenting one row:
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsTry:
filter < 0 =
COUNTROWS(
CALCULATETABLE(VALUES(Table[Part]), FILTER(VALUES(Table[Part]), [your measure] < 0)))
Add this measure to the filter pane and set the value to = 1
Proud to be a Super User!
Paul on Linkedin.
Hi @PaulDBrown ,
This does not work either.
I think I need to share the file, here is a link:
https://drive.google.com/file/d/1JzT1Cwqh5anJ_A6dvVuWdGGpz73qj3Mt/view?usp=sharing
Hi @DCELL ,
On the filter pane on the matrix select the measure and then select all the values below 0. Should return correct value has you need.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @MFelix ,
This is what I was trying to explain with
"applying a simple '<0' filter will not give result I'm looking for, because it will filter for <0 at the lowest level of the row hierarchy and then calculate, instead of filtering only after the calculation is complete."
This is the result when filtering the measure for < 0 in the filter pane:
The default behavior, as I understand it, is:
1) Filter
2) Evaluate
But I need
1) Evaluate
2) Filter the result of the evaluation for <0 at the top level of the row hierarchy
This is what I'm looking for:
Hi @DCELL ,
To understand you want to have the lines that have a negative value in any column is that it?
Because looking at the values you are presenting how do I know that the DEF should be present is because those two values are negative?
Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.
If the information is sensitive please share it trough private message.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português@MFelix,
In simple terms, I need Power BI to "Show me parts that will have a projected quantity on hand less than zero at any point in time."
I edited my original post and pasted a link at the end.
Thanks!!
Hi @DCELL ,
You can try the following formula:
Filter_Consolidate =
VAR temp_table =
CALCULATETABLE (
FILTER (
ADDCOLUMNS (
SUMMARIZE ( DemandSupply, DemandSupply[Part], Cal[Fiscal Year], Cal[rDate] ),
"_Consolidated", [Consolidated Planning Data]
),
[_Consolidated] < 0
),
ALL ( Cal ),
ALLEXCEPT ( DemandSupply, DemandSupply[Part] )
)
RETURN
COUNTROWS ( temp_table )
Has you can see below is only presenting one row:
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering 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 |
---|---|
106 | |
98 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |