Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello everyone
I have data set like this ---
ID Apply Offer Enrolment
1 True True False
2 False False False
3 True True True
.
.
.
I want to create a filter has 3 value (Apply, Offer, Enrolment) when I select value, it will automatcially filter the ID = True (as I don't want to see those False ID under seleccted filter)
Any Idea?
Solved! Go to Solution.
As suggested by @amitchandak , You can unpivot the table.
Create a duplicate of the main table and unpivot columns. Then filter the rows as TRUE
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nRR0lFyLCjIqQTS/mlpqUVA2jWvKD8nNzWvRClWJ1rJECgSEhTqikaBpIyAHDdHn2AkOQgXJGmMQ18sAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID", Int64.Type}, {"Apply", type logical}, {"Offer", type logical}, {"Enrolment", type logical}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"ID"}, "Attribute", "Value"),
#"Filtered Rows" = Table.SelectRows(#"Unpivoted Columns", each ([Value] = true))
in
#"Filtered Rows"
Then enable the relationship
Now you can use the attribute column in the FilterTable for filtering the ID.
Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂
ID | Apply | Offer | Enrolment |
1 | TRUE | TRUE | TRUE |
2 | FALSE | TRUE | FALSE |
3 | TRUE | TRUE | TRUE |
Use the below table in a slicer.
Type |
Apply |
Offer |
Enrolment |
Create a DAX measure
FilterMeasure =
var _slicerselection= CALCULATE(SELECTEDVALUE(SlicerTable[Type]))
var _apply= IF(SELECTEDVALUE(MyTable[Apply])=TRUE(),"show","hide")
var _offer=IF(SELECTEDVALUE(MyTable[Offer])=TRUE(),"show","hide")
var _enrolment= IF(SELECTEDVALUE(MyTable[Enrolment])=TRUE(),"show","hide")
var result= SWITCH(_slicerselection,"Apply",_apply,"Offer",_offer,"Enrolment",_enrolment)
return result
You can use this measure in the visual level filter to show only required IDs.
Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂
it's awsome!!! I tried as you said, it works in visual level --- but wonder how can I make it work in page level (this page?)
As suggested by @amitchandak , You can unpivot the table.
Create a duplicate of the main table and unpivot columns. Then filter the rows as TRUE
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nRR0lFyLCjIqQTS/mlpqUVA2jWvKD8nNzWvRClWJ1rJECgSEhTqikaBpIyAHDdHn2AkOQgXJGmMQ18sAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID", Int64.Type}, {"Apply", type logical}, {"Offer", type logical}, {"Enrolment", type logical}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"ID"}, "Attribute", "Value"),
#"Filtered Rows" = Table.SelectRows(#"Unpivoted Columns", each ([Value] = true))
in
#"Filtered Rows"
Then enable the relationship
Now you can use the attribute column in the FilterTable for filtering the ID.
Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂
@baron33 , Unpivot might help to do that better
https://radacad.com/pivot-and-unpivot-with-power-bi
User | Count |
---|---|
93 | |
83 | |
77 | |
73 | |
66 |
User | Count |
---|---|
115 | |
104 | |
93 | |
64 | |
61 |