How can I have a measure adhere to slicer filters?

1
votes

I'm trying to figure out how to get a measure to adhere to the filter set by a slicer in Power BI.

My DAX query is: Block Time Cost = SUMX( FILTER(v_Invoice_Line_Items, v_Invoice_Line_Items[IV_Item_RecID]=9), v_Invoice_Line_Items[billable_ext_price_amount])

I know very, very little about DAX so my initial query may be way off base.

It calculates as I expect, but when filter with a date range silcer the value does not update as expected or at all.

I'm pulling my data from two views in the same database, v_Invoice and v_Data_Combined. I have a page level filter on the row Record_Description to limit the data to the types I'm looking for and the measure pulls it's data from rows in the v_Data_Combined view.

The rows in v_Invoice are below.

v_Invoice

A sample copy is here.

and the rows for v_Data_Combined, if you click they will enlarge.

v_Data_Combined 1

v_Data_Combined 2

v_Data_Combined 3

A sample copy is here.

I have no relations set between the views.

How can I have a measure adhere to slicer filters?

1
powerbidaxslicers
By default, a measure does adhere to slicers. If it's not, there could be a number of causes, so more information about your data & your data model would be helpful. What's the table/column you're slicing on? Do you have any example data (including what the measure is calculating vs. what you expect it to be calculating)? Finally, how are your tables related to each other? – Leonard
@Leonard I've added what I can, I can't show any of the raw data as it is confidential. – Persistent13
@Persistent13 It might be worth generating a small reproducible example to share, with just a few columns per table. – user5226582
Yes, user5226582 is absolutely right - a small reproducible example with a few rows of fake data from each table (and just the main columns) is all that's needed. There are a lot of things you can do in Power BI that can affect whether what you see is what other people see, so sharing exactly how to reproduce what you see in a minimum number of steps is very valuable. – Leonard
I've added some sample data after getting approval. Let me me know if you need more. – Persistent13

1 Answers

0
votes

The slicer has to be on the same table as the measures you're filtering, or on a table related to that table. If your slicer is on a column in v_Invoice and your data is from v_Data_Combined - and the 2 tables are unrelated in Power BI, the slicer from one table will have no effect on the data from the other table.

Without sample data (which can be fake data), it's hard to make further recommendations.

However, if the two tables you have aren't really related to each other, then I would recommend exploring the possibility of "lookup" tables. E.g. if you have Company_Name in both tables, then you might add a 3rd table that is a unique list of companies (their name, address, etc). Then, when you want to slice by company you would slice on this 3rd table. That slicer will then filter both related tables (without having to have the tables related to each other).

You can read more about data modeling in Power BI, and how to design lookup tables, here: https://powerpivotpro.com/2016/02/data-modeling-power-pivot-power-bi/