One slicer to filter multiple columns and count number of records of each column separately

[Andrew17]

Hi,

I have a table that shows data in the below format:

Unique ID	Current Status	Status 1 Completion Date	Status 1 Completion Week	Status 2 Completion Date	Status 2 Completion Week
AA	1	3/20/2020	12
AB	1	3/25/2020	13
AC	1	3/24/2020	13
AD	2	3/20/2020	12	3/21/2020	12
AE	2	3/25/2020	13	3/27/2020	13

And then I have two measures. These measures count the number of total Unique IDs that have values for each Status Completion Date.

With no filter, the measures would look like this:

Count of Status 1 = 5

Count of Status 2 = 2

What I want is to be able to filter based on the week, to see how many unique IDs of each Status were completed in a specific week. So if I selected Week 13 in the slicer, the measures would be:

Count of Status 1 = 3

Count of Status 2 = 1

I'm wondering if there is any way to do this in Power BI? Maybe there's a way to change the structure of the data to make it easier to do something like this that I can't think of?

I can't seem to come across any post who has had a similar question before in the past. I'm welcome to any solution even if it is a lot of work for me to add in!

Thanks!

Andrew

[V-lianl-msft · Accepted solution]

Hi @Andrew17 ,

Create a new unrelated week table and use it as a slicer.

Then create measures to count the unique ID.

count_id1 =
IF (
    HASONEFILTER ( 'Table 2'[week] ),
    CALCULATE (
        COUNT ( 'Table'[Unique ID] ),
        FILTER (
            'Table',
            'Table'[Status 1 Completion Week] = SELECTEDVALUE ( 'Table 2'[week] )
        )
    ),
    CALCULATE (
        COUNT ( 'Table'[Unique ID] ),
        FILTER ( 'Table', NOT ( ISBLANK ( 'Table'[Status 1 Completion Week] ) ) )
    )
)

Here is the sample pbix.

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Andrew17]

Hi @V-lianl-msft 

Thanks again for your help, I've been playing around with this measure, and I can't seem to find a way to edit it to show multiple weeks together because of the HASONEFILTER function. If I wanted to show both week 12 and week 13 (assuming there are more weeks in the dataset), how would I do that?

[Andrew17]

This is exactly what I needed. Thank you so much 🙂

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[camargos88]

Hi @Andrew17 ,

Try this code on Transform Data (Power Query) -> Advanced Editor:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRU0lEyBGJjfSMDIDIyAPGNgIQCGMfqANU4IdSYwtUYo6pxRqgxwaXGBcg0wmYXUMAQSQCs2BWhGNVSoIA5kkBsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Unique ID" = _t, #"Current Status" = _t, #"Status 1 Completion Date" = _t, #"Status 1 Completion Week" = _t, #"Status 2 Completion Date" = _t, #"Status 2 Completion Week" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Unique ID", type text}, {"Current Status", Int64.Type}, {"Status 1 Completion Date", type text}, {"Status 1 Completion Week", Int64.Type}, {"Status 2 Completion Date", type text}, {"Status 2 Completion Week", Int64.Type}}),
#"Unpivoted Only Selected Columns" = Table.Unpivot(#"Changed Type", {"Status 1 Completion Date", "Status 1 Completion Week", "Status 2 Completion Date", "Status 2 Completion Week"}, "Attribute", "Value"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Only Selected Columns", "Attribute", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Attribute.1", "Attribute.2", "Attribute.3", "Attribute.4"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", Int64.Type}, {"Attribute.3", type text}, {"Attribute.4", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Attribute.3"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Attribute.4]), "Attribute.4", "Value"),
#"Replaced Value" = Table.ReplaceValue(#"Pivoted Column"," ",null,Replacer.ReplaceValue,{"Date"}),
#"Changed Type with Locale" = Table.TransformColumnTypes(#"Replaced Value", {{"Date", type date}}, "en-US"),
#"Changed Type2" = Table.TransformColumnTypes(#"Changed Type with Locale",{{"Week", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type2", "Status", each [Attribute.1] & " " & Text.From([Attribute.2])),
#"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"Unique ID", "Current Status", "Status", "Attribute.1", "Attribute.2", "Date", "Week"}),
#"Removed Columns1" = Table.RemoveColumns(#"Reordered Columns",{"Attribute.1", "Attribute.2"}),
#"Changed Type3" = Table.TransformColumnTypes(#"Removed Columns1",{{"Status", type text}})
in
#"Changed Type3"

Ricardo