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.
Need guidance! I'm looking to sum a quantity column, but filter by 2 separate visuals. The difficulty I am having is decrementing the quantity when an added variable is introduced.
I'm looking for the quantity sum in WIP using the ASLY & TEST fields as my current filters. Below I can easily filter to get what quantity is in ASLY (qty 2 based on below), but when I add in a test date, the ASLY quantity doesn't decrement.
I am unsure how to include a statement to decrement? Thoughts?
QTY | ASLY | TEST |
1 | 13-Aug-20 | |
1 | 13-Aug-20 | |
2 |
Solved! Go to Solution.
Hi @bsmcfaden :
Try the below dax expression:
measure=
var _countall=SUMX(FILTER(ALLSELECTED('Customer Orders'(3)),'Customer Orders'[ASLY]<>BLANK()),'Customer Orders'[Qty])
var _counttest==SUMX(FILTER(ALLSELECTED('Customer Orders'(3)),'Customer Orders'[ASLY]<>BLANK()&&'Customer Orders'[Test]<>BLANK()),'Customer Orders'[Qty])
Return
_countall-_counttest
If the above doesnt work,could you pls upload your .pbix file to onedrive business and share the link with us?
HI @bsmcfaden ,
Your requirement is not clear. Please detail it.
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Thanks,
Pragati
Hi @Pragati11
Of course! I am using a basic Card visual to calculate the sum of a quantity column (shown below):
This is calculated using two filters from an Excel file (fields shown below):
1. ASLY column is NOT BLANK
2. TEST column is BLANK
Challenge: When a TEST date is added, the Card visual does not reduce/decrement the quantity.
Example: The TEST date was added, but the Card visual did not get reduced by qty 1.
Please let me know if this data is sufficient for support. Thank you!
Hi @bsmcfaden :
Try the below dax expression:
measure=
var _countall=SUMX(FILTER(ALLSELECTED('Customer Orders'(3)),'Customer Orders'[ASLY]<>BLANK()),'Customer Orders'[Qty])
var _counttest==SUMX(FILTER(ALLSELECTED('Customer Orders'(3)),'Customer Orders'[ASLY]<>BLANK()&&'Customer Orders'[Test]<>BLANK()),'Customer Orders'[Qty])
Return
_countall-_counttest
If the above doesnt work,could you pls upload your .pbix file to onedrive business and share the link with us?
@bsmcfaden , Try
calculate(countrows(Table),isblank(Table[Test]))
Here the assumption is Asly is driving the date filter or joined with date table.
Also, refer how to join two dates with same date table. And Check open between dates.
Hi @bsmcfaden ,
Can you please share the calculation that you did to populate value in your card visual?
Thanks,
Pragati
Hi @bsmcfaden ,
Rather than putting visual level filters this way on your card visual, try creating a DAX measure below:
CalcMeasure = CALCULATE(SUM(tablename[Qty]),
FILTER(ALLSELECTED(tablename), tablename[ASLY] <> BLANK() && tablename[TEST] = BLANK())
)
Replace tablename in above expression with your table name.
Then move this measure to your card visual.
Thanks,
Pragati
Here's what I get:
Question: is this measure just a replacement for the visual level filters?
Hi @bsmcfaden ,
If you see my suggested DAX expression clearly, you missed out to close a bracket after SUM function.
Thanks,
Pragati
Yes, thank you I did miss the SUM bracket. The DAX measure does replace the current visual level filters, but does not resolve my original issue.
If I add a TEST date, how can I get this same card visual to reduce the quantity?
No slicers. Here's a more complete picture. As you can see, I'm trying to see where the units remaining to ship are in the process. The DAX measure you provided does show the correct quantity in my WIP Assembly card, but when a TEST date is added to move the product to WIP Test, the WIP Assembly card does not decrement.
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 |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |