Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

How to count based on condition?

Hello

I have the following question. My test fact table1

 

IdDimProcess   IdDimPerson1  IdDimPerson2  IdTime
1              123           123           700
2              123           456           720
3              456           456           800
4              123           789           930

I require to make two measures:

Person1 calculation is just the count of all occurrences of IdDimPerson1. Result In this case:

123 - 3
456 - 1

 Person2 calculation is the count of all occurrences of IdDimPerson2 when IdDimPerson1<>IdDimPerson2. Result In this case:

456 - 1
789 - 1

The main issue is that i have a slider poiting to the Persons dimension which is related to IdDimPerson1. So once i check a specific person, the measure of Person2 will show 0

 

Will appreciate your help

Need Help
  • v-zhangti's avatar
    v-zhangti
    4 years ago

    Hi, Anonymous 

     

    You can try the following methods.

    Measure:

    Person1 =
CALCULATE (
    COUNT ( 'Table 1'[IdDimPerson1] ),
    FILTER (
        ALL ( 'Table 1'[IdDimPerson1] ),
        [IdDimPerson1] = SELECTEDVALUE ( 'Table 1'[IdDimPerson1] )
    )
)

    Column:

    Column = IF([IdDimPerson1]<>[IdDimPerson2],[IdDimPerson2],BLANK())

    Measure:

    Person2 =
CALCULATE (
    COUNT ( 'Table 1'[Column] ),
    FILTER (
        ALLSELECTED ( 'Table 1' ),
        [Column] = SELECTEDVALUE ( 'Table 1'[IdDimPerson2] )
    )
)

    What kind of slider are you talking about? Can you provide example data?

     

    Best Regards,

    Community Support Team _Charlotte

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

1 Reply

  • v-zhangti's avatar
    v-zhangti
    Community Support
    4 years ago

    Hi, Anonymous 

     

    You can try the following methods.

    Measure:

    Person1 =
CALCULATE (
    COUNT ( 'Table 1'[IdDimPerson1] ),
    FILTER (
        ALL ( 'Table 1'[IdDimPerson1] ),
        [IdDimPerson1] = SELECTEDVALUE ( 'Table 1'[IdDimPerson1] )
    )
)

    Column:

    Column = IF([IdDimPerson1]<>[IdDimPerson2],[IdDimPerson2],BLANK())

    Measure:

    Person2 =
CALCULATE (
    COUNT ( 'Table 1'[Column] ),
    FILTER (
        ALLSELECTED ( 'Table 1' ),
        [Column] = SELECTEDVALUE ( 'Table 1'[IdDimPerson2] )
    )
)

    What kind of slider are you talking about? Can you provide example data?

     

    Best Regards,

    Community Support Team _Charlotte

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