Use changing or dynamic values of Measure as Slicer

[Anonymous]

Hello,

I understand we can use disconnected table approach (probably the only way?) to use Measure values (texts in my case) in Slicer. It works nicely when the values in Measure are required to be shown in a Slicer all the time, i.e. the selection list is "static".
In my case I want the Slicer values to show the changing calculated value of Measure which can be different based on other slicers' selections during runtime i.e. selection list is "dynamic". Any suggestions on how this can be achieved?

Thanks

Nirmit

[Anonymous]

Hi @amitchandak, Thanks for your answer. Apparently I got stuck in the first step of filtering the data on Measure value using disconnected table itself. Not able to set it up for my scenario. I did watch your youtube video for creating mesaure slicer and for dynmaic segmentation as well (thanks for great videos though) but my problem is - in my scenario the Mesaure in question is not calculated from numeric values (like sales amount) but it is a text categorization purely based on other column text values and another slicer. Thus I am not able to just set it up. Could you help me get through this please?

Below is a very simplified version of my scneraio. There are many more fields in both the Dim and Fact tables. Dim table has thoudands and Fact has lakhs of records.
Dim_Asset

Asset	Owner
A	Company1
A	Company2
B	Company1
C	Company2
C	Company3

Fact_Incident

Asset	IncidentType	Measure_AssetType
A	Hardware
B	Hardware
C	OS

Measure_AssetType is required to be calculated dynamically based upon selected Owner, say first slicer, and reported IncidentType from fact table.
Lastly I want to show the values of Measure_AssetType within second slicer and allow users to filter all visuals of the multiple Pages for that value. Now since every visual is at different granularity and measure output is a text, facing challenge in defining such a measure which can be used with disconnected table approach.

Below is the logic required for Measure_AssetType:
IF (Dim_Asset[Owner] = "Company1" && Fact_Problem[ProblemType] = "Hardware") THEN "Workstation"
ELSEIF (Dim_Asset[Owner] = "Company1" && Fact_Problem[ProblemType] = "OS") THEN "Server"
(Dim_Asset[Owner] = "Company2" && Fact_Problem[ProblemType] = "Hardware") THEN "Deprecated"
ELSEIF (Dim_Asset[Owner] = "Company2" && Fact_Problem[ProblemType] = "OS") THEN "Fixed"
ELSE ""

As a result, I want a slicer which shows below values and filters all visuals data as applicable.

When Company1 is selected in Owner slicer:
Slicer_Measure_AssetType

Workstation

Server

When Company2 is selected in Owner slicer:
Slicer_Measure_AssetType

Deprecated

Fixed

When Company3 is selected in Owner slicer:
Slicer_Measure_AssetType

How can this be achieved? Thanks in advance for help.

Regards

Nirmit

[Anonymous]

Hi @Anonymous 

Here I create two same table [Dim_Asset] and [Fact_Incident] to have a test. We couldn't add a measure into Slicer.

Try to create a calculated table by dax to for slicer.

Slicer_Measure_AssetType = 
{"Workstation","Server","Deprecated","Fixed",""}

Add a calcualted column in this table.

Owner = 
SWITCH (
    TRUE (),
    Slicer_Measure_AssetType[Value] IN { "Workstation", "Server" }, "Company1",
    Slicer_Measure_AssetType[Value] IN { "Deprecated", "Fixed" }, "Company2"
)

Then create a relationship between Dim_Asset[Owner] and Slicer_Measure_AssetType[Owner].

Then create a measure to calculate Measure_AssetType by your logic.

Measure_AssetType = 
VAR _AssetType =
    SWITCH (
        MAX ( Dim_Asset[Owner] ),
        "Company1",
            SWITCH (
                MAX ( Fact_Incident[IncidentType] ),
                "Hardware", "Workstation",
                "OS", "Server"
            ),
        "Company2",
            SWITCH (
                MAX ( Fact_Incident[IncidentType] ),
                "Hardware", "Deprecated",
                "OS", "Fixed"
            )
    )
VAR _SelectValue =
    VALUES ( Slicer_Measure_AssetType[Value] )
RETURN
    IF ( _AssetType IN _SelectValue, _AssetType, BLANK () )

Result is as below.

Select Company2:

Then select "Deprecated"

Best Regards,
Rico Zhou

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

[Anonymous]

Hi @Anonymous 

Thank you for detailed repsonse. Your solution seems to be working at table level visual when all fields are added to the view. However as soon as any field is taken out, the Measure, being a measure using MAX, is recalculated and returns a different value. That is a problem.

For example, in below picture, I have added another table visual and taken Incident Type out from this view, the Measure_AssetType recalculates to "Server". While I wanted to retain the same value "Workstation" at all levels.
In summary, in my case, I want the 'field' AssetType to return a different value at most granular level depending upon selection in Owner Slicer and as per formula given in my above post, and then it should retain that computed value at all aggreation levels as long as the Owner Slicer selection is not changed. Hope it is clear. How can that be achieved?

Thanks

Nirmit

[Anonymous]

Hi @Anonymous ,

I think you can solve this issue by change the relationship direction between "Fact_Incident" and "Dim_Asset" from single to both.

Result is as below.

Best Regards,
Rico Zhou

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

[Anonymous]

Hi @Anonymous 

Thanks again for response. This unfortunately still isn't working for me. Main reason being I have multiple records of same asset in fact table and in reality my data model is actually not a simple star schema. It is kind of snow flake built due to RLS requirement.
Sorry if my explanation was not clear or if was not complete, clarifying it more below:

1. Data model I shared is a very scaled down version of my big data model. My dim and fact tables are not directly connected rather there are multiple dim tables in between. It goes like this: (Owner is Vendor. I have changed the table names slightly to make it understandable)
dim_User_Vendor > dim_Vendor > dim_Vendor_Profile > dim_Profiles > dim_Asset_Profiles > dim_Asset > Fact_incident

dim_Asset does not contain the Vendor (Owner) field but it is located in dim_Vendor

I have Row Level Security implemented on very first table dim_User_Vendor and it flows all the way to the Fact table based on USERPRINCIPALNAME(). Sharing below concerned part of my model.

2. In my sample data, I indicated only one record per Asset in Fact_Incident table perhaps due to which in your PBIX file it is at the one-side of the relationship. In reality, fact table contains multiple records per asset and is at many-side of relationship. To test, I added two more records in your PBIX file in fact table one for each asset A and B. The measure again re-calculates to a different value at a different aggregation level. Please see below.

I have strong feeling that I want my AssetType to behave just like a "Column" (i.e. no aggregation) after it is calculated based on Vendor/Owner selection. Do you think it is a contradictory requirement to the way Measures work and cannot be achieved using a Measure? If yes then only option which I can think of is to split and prepare the Fact table data seperately one for each vendor and create a calculated column AssetType in each of split fact tables (and maybe Union all together afterwards). With that approach I will end up with duplicate data rows across vendors which again makes it complicated to handle.

Looking forward to your thoughts.

Regards

Nirmit

[amitchandak]

@Anonymous , Then in that case the measure table you create needs to have a reference for that say column 2

Then you can use visual level filter in slicer

countrows(filter(MeasureTable, MeasureTable[Column 2] in allselected(Table2[Column 2] ) ) )