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 gives the group of the previous month. Then I want the measure to count the number of rows where multiple filter conditions are met. I want to count rows where Dank rank =2, Status and Previous Status = "Not Normal" and where Type = "Task". So this would return a 1 because there is only one row where all conditions are met. But I want the measure to update based on what the user selects as the current month. If the user does not select a month, then I want to show blank for the measure.

 

IDStatusDate RankPrevious StatusMonthType
ANormal3No previousMarTask
BNormal3No previousMarTask
CNot Normal3No previousMarTask
DNot Normal3No PreviousMarNot Task
ANot Normal2NormalMay Task
BNormal2NormalMay Not Task
CNormal2Not NormalMay Not Task
DNormal2Not NormalMay Task
ANot Normal1Not NormalJuneTask
BNot Normal1NormalJuneTask
DNormal1NormalJuneTask
ENormal1 JuneTask

 

This is the measure I have so far. But it doesnt work. I am not sure how to modify. I also want this to be able to work if the measure is placed on a bar visual that is grouped by another column on the x-axis. 

 

VAR SelectedMonthRank =
    CALCULATE(
        MAX('Table'[Date Rank]),
        FILTER(
            ALL('Table'),
            'Table'[Month] = SELECTEDVALUE('Table'[Month])
        )
    )
VAR PreviousMonthDateRank = SelectedMonthRank + 1
RETURN
IF(
    ISBLANK(SelectedMonthRank),
    BLANK(),
    CALCULATE(
        COUNTROWS('Table'),
        FILTER(
            'Table',
            'Table'[Date Rank] = PreviousMonthDateRank
        )
    )
)
  • 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

6 Replies

  • 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.