Forum Discussion

sinanalmac's avatar
sinanalmac
Resolver I
6 years ago
Solved

Filter to count the calculation using slicer from another table

Hi   all, I need to  preapare a report  about Availability. I 'm trying to Calculate count  product no  by  availability= 'yes ' / calculate  count  productcode   by availability all I aplied this ...
Need Help
Show and Tell
Tutorial Requests
  • v-lionel-msft's avatar
    v-lionel-msft
    6 years ago

    Hi sinanalmac ,

    The relationship of your tables in the data model is right, but you have create wrong column and measure:

    I have created a measure like this:

    Measure = 
VAR x = 
CALCULATE(
    COUNT('Table'[ProductCode]),
    FILTER(
        'Table',
        'Table'[Availability] = "yes"
    )
)
VAR y = 
CALCULATE(
    COUNT('Table'[ProductCode]),
    ALLSELECTED(Week[WeekOrder])
)
RETURN
x/y

    I don’t know that what does your ‘WeekOrder’ column want to calculate.
    Is this what you want?

    WeekOrder = 
RANKX (
        FILTER(
            Week,
            Week[Week Year] = EARLIER(Week[Week Year])
        ),
        Week[WeekNumber],
        ,
        ASC,
        DENSE
)

     

    Best regards,
    Lionel Chen

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-lionel-msft's avatar
v-lionel-msft
Community Support
6 years ago

Hi sinanalmac ,

The relationship of your tables in the data model is right, but you have create wrong column and measure:

I have created a measure like this:

Measure = 
VAR x = 
CALCULATE(
    COUNT('Table'[ProductCode]),
    FILTER(
        'Table',
        'Table'[Availability] = "yes"
    )
)
VAR y = 
CALCULATE(
    COUNT('Table'[ProductCode]),
    ALLSELECTED(Week[WeekOrder])
)
RETURN
x/y

I don’t know that what does your ‘WeekOrder’ column want to calculate.
Is this what you want?

WeekOrder = 
RANKX (
        FILTER(
            Week,
            Week[Week Year] = EARLIER(Week[Week Year])
        ),
        Week[WeekNumber],
        ,
        ASC,
        DENSE
)

 

Best regards,
Lionel Chen

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

sinanalmac's avatar
sinanalmac
Resolver I
6 years ago

hi  v-lionel-msft  thank for your solution and your interested. 


Your  solution Turn on a light in my mind

Thank you for helping me?

I 've also  found  anather solution  regarding this.   I just  want to share  it
KEEPFILTERS AND REMOVEFILTERS functions are also  working.

CountYes = CALCULATE(count('Table'[ProductCode]),'Table'[Availability]="Yes")/CALCULATE(Count('Table'[ProductCode]),KEEPFILTERS(table[date].[Year])