Measure behavior based on grid columns

[oracledw]

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.

[v-lili6-msft]

HI  @oracledw 

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

[amitchandak]

@oracledw , 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/

[Greg_Deckler]

@oracledw - 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?

[oracledw]

@Greg_DecklerI provided the image in above post.

[Greg_Deckler]

@oracledw - 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.