Unchanged Results by Multiple Filters

[Kebas_Leech]

Hello everyone,

I need to calculate the sum of a distinct count of  ( KZ_[OT] ). This sum is displayed in a table according to two filters, ( KZ_[Product] ) and (  KZ_[Type] ). The thing is, I have a Segmentation Data Bar by Month and I need the KZ_[measure] to not change after I select a specific Month of that bar. 

My Code:

var testing =

Calculate(

                sumx(

                        summarize( 

                            KZ_,

                            KZ_[Year]  

                        ),

                            DISTINCTCOUNT(KZ_[OT])

                    ),

                ALL(KZ_[Month]),ALLSELECTED(KZ_[Product]), KEEPFILTERS(KZ_[Year] = 2022)

        )

return testing

Result:  

The code works great if I dont select a specific month in the Segmentation Data Bar. If I do it, the value of the KZ_[measure] is properly calculated but according to the month I selected, instead of keeping the value for ALL months.

Expected Result:

Same thing if I select only a month

Thanks!

[v-yiruan-msft]

Hi @Kebas_Leech ,

I created a sample pbix file(see the attachment), please check if that is what you want to get.

1. Create a month dimension table and don't create any relationship with the fact table 'KZ_' using "Enter data" method

2. Create a measure as below to get the count of OT

Measure = 
VAR _tab =
    SUMMARIZE (
        'KZ_',
        'KZ_'[Product],
        'KZ_'[Size],
        "@count", CALCULATE ( DISTINCTCOUNT ( 'KZ_'[OT] ) )
    )
RETURN
    SUMX ( _tab, [@count] )

3. Create a matrix visual

If the above one can't help you get the desired result, please provide some sample data in your table 'KZ_' (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[Kebas_Leech · Accepted solution]

I found the solution:

var testing =

Calculate(
          sumx(
               summarize( 
                         KZ_,
                         KZ_[Year]  
                        ),
                 DISTINCTCOUNT(KZ_[OT])
               ),
    ALL(KZ_[Month]),ALL(KZ_[Product]), ALL(KZ_[Product_Line]) , KEEPFILTERS(KZ_[Year] = 2022)
)
return testing

Obviously this code works for my particularly case however what i got from this was:

- The code wasn't working properly because allselected only works for the selected cases in the filter, so when I chose January, in the Segmentation Bar, he was only filtering for the products of January.. even if using all([Kz_[Month]).

- The second thing was (this one i still dont understand the reason behind it): I had my month column changed to Kz_[MonthName] and I was using it in the Segmentation Bar. However, when i changed this column to the original column kz_[Month] (numeric) it worked.

[v-yiruan-msft]

Hi @Kebas_Leech ,

It's glad to hear that you got the solution. And thanks for sharing your solution here. It will help the others in the community find the solution easily if they face the same problem as yours.  Thank you.

Best Regards

[Kebas_Leech]

Hi @v-yiruan-msft ,

I forward the link: https://drive.google.com/file/d/19IfUFwaAMPal-8fpiGtPO4NHKIfc0-EJ/view?usp=share_link

As you can see I have my measure: [Calculation OTs] and yours: [Measure].

I intend to have the sum of all distinct OTs independently of the Months and Products while keeping that table structure.

What bothers me is that while doing this example file, my measure works even if I change the months in the tab "Changed Segmentation Bar" and this isn't happening in the original file.. and that was the reason why I needed help 😓. So in the original is basicly calculating the sum of all the OTs for the specific month I select

With your experience any clue why this might be happening?

Thank you again.