Count rows based on slicer selection is showing blank

[char23]

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.

ID	Status	Date Rank	Previous Status	Month	Type
A	Normal	3	No previous	Mar	Task
B	Normal	3	No previous	Mar	Task
C	Not Normal	3	No previous	Mar	Task
D	Not Normal	3	No Previous	Mar	Not Task
A	Not Normal	2	Normal	May	Task
B	Normal	2	Normal	May	Not Task
C	Normal	2	Not Normal	May	Not Task
D	Normal	2	Not Normal	May	Task
A	Not Normal	1	Not Normal	June	Task
B	Not Normal	1	Normal	June	Task
D	Normal	1	Normal	June	Task
E	Normal	1		June	Task

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

        )

    )

)

[Jihwan_Kim]

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]

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]

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]

Sorry, just realized that I left a column of data from this. 

Want to compare the columns Status and Previous status. If the user selects Jun, 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.

[Anonymous · Accepted solution]

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

[char23]

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