Forum Discussion

char23's avatar
char23
Icon for Helper II rankHelper II
2 years ago
Solved

Count rows based on slicer selection is showing blank

I am trying to write a measure. I have the following table. I want the user to be able to select a month. If the user selects June. Then I want previous month = 2 which is May. 1 plus the date rank g...
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi char23 ,

     

    You can try to disconnect the relationship between Table1 and Table 2:

    then use this measure:

    MEASURE = 
VAR __selected_month_rank =
    SELECTEDVALUE ( 'Table2'[Date Rank] )
VAR __result =
    IF (
        ISBLANK ( __selected_month_rank ),
        BLANK (),
        IF ( 
            SELECTEDVALUE( 'Table1'[Date Rank] )
                IN { __selected_month_rank, __selected_month_rank + 1 },
            CALCULATE (
                COUNTROWS ( 'Table1' ),
                'Table1'[Status] = "Not Normal"
                    && 'Table1'[Previous Status] = "Not Normal"
                    && 'Table1'[Type] = "Task"
            )
        )
    )
RETURN
    __result

    Output:

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
2 years ago

Hi,

I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.

Please check the  below picture and the attached pbix file whether it suits your requirement.

 

 

 

 

expected result measure: = 
VAR _SelectedYearMonthEndDate =
    SELECTEDVALUE ( 'Calendar'[Year-Month sort] )
VAR _PreviousYearMonthEndDate =
    EOMONTH ( _SelectedYearMonthEndDate, -1 )
VAR _t =
    CALCULATETABLE (
        Data,
        FILTER (
            ALL ( 'Calendar' ),
            'Calendar'[Year-Month sort] = _PreviousYearMonthEndDate
        ),
        'Status'[Status] = "Not Normal",
        'Type'[Type] = "Task"
    )
RETURN
    COUNTROWS ( _t )

 

  • char23's avatar
    char23
    Icon for Helper II rankHelper II
    2 years ago

    Thank you! This is not exactly how I have my table/file arranged, but this somewhat works for me (especially using the calculatetable function), but when I add the measure to a bar graph, it shows the same total for each group. Is there anyway I can fix this? 

    • Jihwan_Kim's avatar
      Jihwan_Kim
      Icon for Super User rankSuper User
      2 years ago

      Hi,

      Thank you for your message.

      Please share your sample pbix file's link and the expected outcome of how the barchart looks like (which columns are used for the bar chart axis, and how the result looks like).

      Thank you.

      • char23's avatar
        char23
        Icon for Helper II rankHelper II
        2 years ago

        Hello, thank you very much for your help. I don't think I can upload, but I have included snippets of how my tables are in powerbi and an example of the clustered column visual I am trying to create with the measure. Also, if possible, whenever the user selects a current month to view, I want to automatically populate the second most current month (like in my visual, Jun is selected, so May data is also shown). Thank you again!

         

        Table1

        Table2

         

        The visual I want to create