Inconsistency between measure result and filter result

JesperBT — Wed, 06 Mar 2024 12:49:10 GMT

I have run into a problem with my measures. I am selftaught and learning each day, so this might be the complete wrong approch to this problem, so if there is a more simple way to archieve this, please feel free to suggest that.

I have 6 cards. 12 measures. First card is a sum of the two cards next to it. Second card is a filtered measure and the third one is also a filtered measure but the direct oppsite of the second one.

First measure:

SRP1_total =

VAR Count1 =

CALCULATE(

DISTINCTCOUNT('fct_MissingArticles'[OrderNumber]),

fct_MissingArticles[dim_IsActualShipNodeInv.ShipNodeInv] <> 31 && 'fct_MissingArticles'[dim_IsActualShipNodeInv.ShipNodeInv] <> 76,

fct_MissingArticles[SDS0001.SRP] = 1

)

VAR Count2 =

CALCULATE(

DISTINCTCOUNT('fct_MissingArticles'[OrderNumber]),

('fct_MissingArticles'[dim_IsActualShipNodeInv.ShipNodeInv] = 31 || 'fct_MissingArticles'[dim_IsActualShipNodeInv.ShipNodeInv] = 76),

fct_MissingArticles[SDS0001.SRP] = 1

)

RETURN Count1 + Count2

Second measure:

SRP1_store =

CALCULATE(

DISTINCTCOUNT('fct_MissingArticles'[OrderNumber]),

fct_MissingArticles[dim_IsActualShipNodeInv.ShipNodeInv] <> 31 && 'fct_MissingArticles'[dim_IsActualShipNodeInv.ShipNodeInv] <> 76,

fct_MissingArticles[SDS0001.SRP] = 1

)

Third measure:

SRP1_cdc =

CALCULATE(

DISTINCTCOUNT('fct_MissingArticles'[OrderNumber]),

('fct_MissingArticles'[dim_IsActualShipNodeInv.ShipNodeInv] = 31 || 'fct_MissingArticles'[dim_IsActualShipNodeInv.ShipNodeInv] = 76),

'fct_MissingArticles'[SDS0001.SRP] = 1

)

So as you can see, the second measure has a filter on everything that isn't dim_IsActualShipNodeInv.ShipNodeInv = 31, 76 and the third measure is where the filter is 31 or 76. So far so good.

I have created two slicers, one is filtering the Unit and the other is filtering the Fulfilment Unit (dim_IsActualShipNodeInv.ShipNodeInv).

I have also created to tables, so I could analyze the data in more depth and these two tables are filtering correct, when using the slicers, but my measures are NOT.

This is the result:

The top 3 cards are these measures. and the bottom 3 cards, is only using a measure that is countdistinct(Ordernumber) and then I did some visual filters on each one. The result when using the visual filters are correct (there are 4 unique orders with 7 articles, one per row).

I need to somehow, get the values based on the filters, but I also need to be able to get the percent difference between them (which is why I tried to create the measures in the first place). What is not shown here, is that there is a second group of measures that are calculating with fct_MissingArticles'[SDS0001.SRP] = 4, but the measures are identical except for the SRP number and the percent on the first card, is the difference between the first card and the "forth" card that can't be seen in the screenshot.

I hope it makes sense and you are able to support me with this.

Thanks in advance,

Re: Inconsistency between measure result and filter result

lbendlin — Wed, 06 Mar 2024 23:20:12 GMT

Looks like your second and third measure are a repetition of the first one. You can drop the first measure and add the other two where needed.

First measure:

SRP1_total = SRP1_store + SRP1_cdc

Learn about IN { } and NOT IN { } - these can simplify your measures.

Second Measure:

SRP1_store = CALCULATE( DISTINCTCOUNT('fct_MissingArticles'[OrderNumber]), fct_MissingArticles[dim_IsActualShipNodeInv.ShipNodeInv] NOT IN { 31,76 } , fct_MissingArticles[SDS0001.SRP] = 1 )

Third measure:

SRP1_cdc = CALCULATE( DISTINCTCOUNT('fct_MissingArticles'[OrderNumber]), ('fct_MissingArticles'[dim_IsActualShipNodeInv.ShipNodeInv] IN { 31,76 }, 'fct_MissingArticles'[SDS0001.SRP] = 1 )

topic Inconsistency between measure result and filter result in DAX Commands and Tips

Inconsistency between measure result and filter result

Re: Inconsistency between measure result and filter result