Forum Discussion

Kebas_Leech's avatar
Kebas_Leech
Icon for Helper I rankHelper I
3 years ago
Solved

Unchanged Results by Multiple Filters

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 ha...
  • Kebas_Leech's avatar
    Kebas_Leech
    3 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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's avatar
Kebas_Leech
Icon for Helper I rankHelper I
3 years ago

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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