Dynamic Filter

[baron33]

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?

[nandukrishnavs]

@baron33 

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 🙂

[baron33]

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?)

[nandukrishnavs · Accepted solution]

@baron33 

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 🙂

[amitchandak]

@baron33 , Unpivot might help to do that better

https://radacad.com/pivot-and-unpivot-with-power-bi