Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

User Input - What If Parameter - Count Rows

Jump to Best Answer
  • 1.  User Input - What If Parameter - Count Rows

    Bronze Contributor
    Posted 11 days ago
    I created two parameters that a user can update with date ranges



    I want to use the values to count the number of orders that were opened and closed during the time frames:

      I'm having trouble applying the filter from the user parameters.  I would like to count the order if the order date is within the open parameter range and also count the number of orders that were closed during the closed parameter range.
    Thanks


    ------------------------------
    Clark Bethancourt
    ------------------------------
    Academy - Online Interactive Learning from Experts


  • 2.  RE: User Input - What If Parameter - Count Rows

    Bronze Contributor
    Posted 10 days ago

    Hi Clark,
    You should make this work using slicer, no need for parameters. You should use parameters in Power Query if you would like to filter data source data based on users' selection.

    But if your goal is to give users option to dynamically change ranges for date opened/closed slicers should do the work.

    Create 2 slicers: one for date opened, second for date closed (and make their behaviour "between".

    After that create 2 measures:

    Number of Opened orders = CALCULATE(COUNTROWS('Table'),FILTER(ALL('Table'),'Table'[Open Date]>=min('Table'[Open Date]) && 'Table'[Open Date]<=MAX('Table'[Open Date])))

    Number of Closed orders = CALCULATE(COUNTROWS('Table'),FILTER(ALL('Table'),'Table'[Closed Date]>=min('Table'[Closed Date]) && 'Table'[Closed Date]<=MAX('Table'[Closed Date])))

    This is the result:


    ------------------------------
    Nemanja Andic
    BI developer
    Belgrade
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 3.  RE: User Input - What If Parameter - Count Rows
    Best Answer

    Top Contributor
    Posted 10 days ago

    Hi @Clark Bethancourt,

    Sorry if my syntax is wrong, but you can use something like this:

    Count Open =
    var min_start = ***define min start using MIN() Function***
    var max_start = ***define max start using MAX() Function***
    Return
    CALCULATE( COUNTROWS(Table1), Table1[Open Date​] >= min_start && Table1[Open Date​] <= max_start)


    Count Close =
    var min_end = ***define min end using MIN() Function***
    var max_end = ***define max end using MAX() Function***
    Return
    CALCULATE( COUNTROWS(Table1), Table1[Close Date​] >= min_end && Table1[Close Date​] <= max_end)

    Also here is video from on how to pass on parameters from excel.

    https://youtu.be/iiNDq2VrZPY

    I hope this works.

    Thank you,



    ------------------------------
    Vishesh Jain
    Owner
    VR Construction
    ------------------------------

    Academy - Online Interactive Learning from Experts