Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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.
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?
This is exactly what I needed. Thank you so much 🙂
Hi,
You may download my PBI file from here.
Hope this helps.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |