Help with filters

[Dellis81]

Hello! 

I am needing help in proper calculations of quantities posted in specific accounting ledgers.   I have two measures.   Both measures have linkages to a table called CenterSetup Source - which is a one-to-many relationship between dimensension and fact tables.

Within the Fact table (TAD Consolidated) - have a data column labeled "Source Center ID" that I think should drive the filters for the two measures.

Revenue Quantity:  When Account ID = Source Center Rev Ledger

Service Unit:  Activity CDU = common Source Center ID's in transactional lines

Revenue Quantity = 

VAR Quantity = CALCULATE ( [Quantity],filter('TADConsolidated',TADConsolidated[Account ID]=max('CenterSetup Source'[RevLedger])))

Return
Quantity

The end result, when I toggle a selection in DrillDownExecSummary - I would expect the display in the card visual to display properly

For example - Blading should show revenue quantity of 13.10 and Service CDU's of 9.7

I think the key to these measures is proper filter criteria utilizing the CenterSetup Source datafield.

File link.

https://drive.google.com/file/d/1ZgbYajOB539W2eT_M_VvFrhQV92qZv86/view?usp=sharing

As always, appreciate the support the forum provides!

[Dellis81]

Ok, thank you!

I will rethink how I have things setup.   I have reorganized and using the CenterSetup Source vs going thru the IncomeBalance table.   That helps, but moves away from terminology users are familiar with.

thanks!

[HotChilli]

The Quantity measure has a 'calculate' in it that you probably don't need.  It might not affect anything but you can probably remove it.

-----------------------------------

The Revenue Quantity measure in the card doesn't display what you want when 'Blading' is chosen in the slicer because a measure is evaluated in context taking in to account slicers and filters.  There isn't any context apart from 'Blading' so 

max('CenterSetup Source'[RevLedger]) returns the max value in the table (44A60) and that doesn't have a matching AccountID in TADConsoldated so you get blank for the measure.   Also RevLedger is a string so if using Max as an aggregation you have to be sure you know how it's going to work.

I haven't looked at the other measure but I assume it also doesn't have enough context to return a value.

----------------

If you click on a row in the main table visual, you then pass more filters to the card and you can get values that way but I don't know if that's what you intend.

-------------

Hope some of that helps.

[Dellis81]

Hey, thank you for looking into.   I realize context is the root cause.   I was hoping there was some DAX magic to make this happen.

You mentioned the max function pulling in the largest value from the CenterSetup Source table.   Is there a way to filter that down to the matching CenterSource ID?
Your thoughts are greatly appreciated!

[V-lianl-msft]

9.7 the corresponding field is null instead of Blading. This is an unchangeable fact. If you want to filter according to RevLedger column, it is recommended that you modify measure again to take RevLedger column as the filter condition, and do not use DrillDownExecSummary slicer. DAX measures are based on row context so any given value from slicers,filters, interactions and so on can influence your result.