Forum Discussion

omelo's avatar
omelo
Helper III
5 years ago
Solved

Distinct Count and count user choice

Hi,

I'm creating a report and would need to give the users the option to include or exclude sub-projects in the project count.

The data is something like:

 

Project   Sub-project

1004

1004          001

1005

1006

 

If selecting to include sub-project, I would use count, the result would be 4.

If selecting no to include sub-projects, I would use count distinct, the result would be 3

 

I've tried creating a mesure, but I realized that  a measure can't be used to filter at the report level.

 

Is there a way to accomplish this, other than creating a dupplicate page and assign the value as needed.

Thank you for your help

Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    omelo ,You need to switch between two measure based on isfiltered

     

    measure =

    var _tab = summarize(Table, Table[project], Table[Project Sub-project])

    return

    countx(_tab, Table[project])

     

    measure 1 = distinctcount(Table[project])

     

    final measure = if(isfiltered (Table[Project Sub-project]), [measure], [measure1])

     

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    omelo ,You need to switch between two measure based on isfiltered

     

    measure =

    var _tab = summarize(Table, Table[project], Table[Project Sub-project])

    return

    countx(_tab, Table[project])

     

    measure 1 = distinctcount(Table[project])

     

    final measure = if(isfiltered (Table[Project Sub-project]), [measure], [measure1])