Forum Discussion
Measure behavior based on grid columns
Hello,
I'm new to Power BI.
I have a measure called COSTING = SUM(TABLE1.COST)
Use Case:
When users drags City from the Location Dimention, then do not add up the cost if ID is -1, so I modified my measure
as follows:
COSTING = CALCULATE(SUM(TABLE1.COST), FILTER (LOCATION.ID <> -1) works file
Here is the issue, but if user drags STATE (coming from the same Loaction Table) in the same grid then I want COSTING measure to ignore LOCATION.ID <> -1, behave like COSTING = SUM(TABLE1.COST)
What can I do to make meassure work
Here is the screen shot of what I'm looking for.
5 Replies
Anonymous - You will need to ISINSCOPE or HASONEVALUE to determine where you "are" and calculate appropriately based on that. Can you post sample data and expected output as text in a table?
Greg_DecklerI provided the image in above post.
Anonymous - Yeah, hand entering data sucks. I don't like to do it if the poster can paste as text and I can copy/paste.
Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
Anonymous , isfiltered /isinscope should able to help you.
like
Measure =
SWITCH (
TRUE,
ISINSCOPE ( Table[State] ), [Costing],
ISINSCOPE ( Table[City] ), [Costing1],
blank()
)refer this example if this can help
https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/
HI Anonymous
Please share your sample pbix file for us have a test, that will be a great help.
which field is used to create a relatonship between TABLE1 and Location table?
Regards,
Lin
